XML Input Stage - help

karthikr25 · Post by **karthikr25** » Thu Apr 18, 2013 2:55 pm

Hi all,

Trying to transform a XML file -> sequential file using XML Input stage. I am able to transform the files successfully

But my requirement is when i do the transformation of xml to seq file i need to replace the data elements code with the MetaData information using xsd

Example : XML contains <emp_grp>2</emp_grp>
i need the output as emp_grp = Maple (i.e, this info is defined in XSD)

Can anyone please provide me some inputs to acheive this

Thanks in advance :D

chulett · Post by **chulett** » Thu Apr 18, 2013 2:59 pm

Welcome!

Normally I would say this would be a lookup into a database table that maps group id to group name or some such. However, if this information is defined in the xsd can you post the section of the xsd that shows that?

karthikr25 · Post by **karthikr25** » Thu Apr 18, 2013 4:10 pm

Thanks for the quick reply

Below is the sample of our XSD
- <xs:schema elementFormDefault="qualified" attributeFormDefault="unqualified" targetNamespace="***" xml:lang="EN" xmlns:xs="http://www.w3.org/2001/XMLSchema">
- 
- <xs:element name="xml">
- <xs:complexType>
- <xs:sequence>
- <xs:attribute name="gn_cd">
- <xs:annotation>
<xs:documentation>Group</xs:documentation>
</xs:annotation>
- <xs:simpleType>
- <xs:restriction base="xs:string">
<xs:maxLength value="4" />
- <xs:enumeration value="0">
- <xs:annotation>
<xs:documentation>Core</xs:documentation>
</xs:annotation>
</xs:enumeration>
- <xs:enumeration value="1">
- <xs:annotation>
<xs:documentation>Closed</xs:documentation>
</xs:annotation>
</xs:enumeration>
- <xs:enumeration value="2">
- <xs:annotation>
<xs:documentation>Type 5</xs:documentation>
</xs:annotation>
</xs:enumeration>
- <xs:enumeration value="3">
- <xs:annotation>
<xs:documentation>Other</xs:documentation>
</xs:annotation>
</xs:enumeration>
</xs:restriction>
</xs:simpleType>
</xs:attribute>

chulett · Post by **chulett** » Thu Apr 18, 2013 5:02 pm

OK... I don't see anywhere where it associates an emp_grp of 2 with 'Maple'. What am I missing?

karthikr25 · Post by **karthikr25** » Sat Apr 20, 2013 1:35 pm

Sorry that was a general example and it not relates with XSD provided above

chulett · Post by **chulett** » Sat Apr 20, 2013 1:45 pm

OK.

What's next? Will you be posting one that does relate? If not, I'm just going to stick my "use a lookup" response from earlier.

karthikr25 · Post by **karthikr25** » Sat Apr 20, 2013 2:04 pm

Below is the XML file -> trying to add the metadata when transforming it to a seq file

Code: Select all

<retrieveResponse xmlns="***" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="**">
      <xml>
        <acct gn_cd="0"/>
        <!--Row Count: 2; Exec Time: 0.039s; Fetch Time: 1us-->
      </xml>
    </retrieveResponse>

eostic · Post by **eostic** » Sat Apr 20, 2013 2:27 pm

it's still not entirely clear what you want to do....in order to parse this using "normal" xml parsing methods, the "metadata" is what controls what will be retrieved.

In this example using the simple old xmlInput Stage, you would have a column in the link called gn_cd, and it's value would be "0"......you could easily put gn_cd into the file on the first line by using "column names on the first line" option.

The metadata would be in the description property for that column, and would have something like

/acct/@gn_cd (because this is an attribute).

Using this as the example, tell us what you "need"....there are certainly ways to do it...possibly not by using the xml stages or by using them in combination with something else.

Ernie

karthikr25 · Post by **karthikr25** » Mon Apr 29, 2013 7:50 am

Eostic sorry for the late reply, was away from work.

I am trying to see whether datastage can add the metadata information available in xsd to the flat file. when converting the web service response xml to flat file using any XMl/WebServices Stage

eostic · Post by **eostic** » Mon Apr 29, 2013 8:27 am

No one understands exactly what you are looking for.....you say "get metadata from the xsd"......there are all kinds of metadata in an xsd (rules, validation types, namespaces, etc. etc. etc.). ...and as Craig noted above, it isn't clear in the example where you are seeing "Maple" what you want to do with it or where you'd locate that value....

....give us the real example and show what you need and we'll all come up with a solution for you, whether it is a lookup or something else.

Ernie

karthikr25 · Post by **karthikr25** » Tue Apr 30, 2013 10:42 am

Ok. below is the XML and XSD

XML

Code: Select all

<retrieveResponse xmlns="****" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://***">
<xml>
<acct acct_gn_cd="0" acct_rcncl_nm_cd="3" acct_resp_ind="0" acct_resp_strt_dt="2013-04-10" acct_no="0000000" gnrc_set_nm_cd="2" man_acct_grp_ind="1" mn_ac_g_ery_rw_ind="1" rcncl_typ_cd="R" sts_cd="O" ublk_al_act_gr_ind="0"/>
<acct acct_gn_cd="0" acct_rcncl_nm_cd="3" acct_resp_ind="0" acct_resp_strt_dt="2013-04-07" acct_no="0000000" gnrc_set_nm_cd="3" man_acct_grp_ind="1" mn_ac_g_ery_rw_ind="0" rcncl_typ_cd="R" sts_cd="O" ublk_al_act_gr_ind="1"/>
</xml>
</retrieveResponse>

XSD

Code: Select all

<?xml version="1.0" encoding="UTF-8" ?> 
- <xs:schema elementFormDefault="qualified" attributErmDefault="unqualified" targetNamespace="http://***" xml:lang="EN" xmlns:xs="http://www.w3.org/2001/XMLSchema">
- <xs:element name="xml">
- <xs:complexType>
- <xs:sequence>
- <xs:element name="acct" minOccurs="0" maxOccurs="unbounded">
- <xs:annotation>
  <xs:documentation>Accounts</xs:documentation> 
  </xs:annotation>
- <xs:complexType>
- <xs:attribute name="acct_no" use="required">
- <xs:annotation>
  <xs:documentation>Account Number</xs:documentation> 
  </xs:annotation>
- <xs:simpleType>
- <xs:restriction base="xs:string">
  <xs:maxLength value="7" /> 
  <xs:whiteSpace value="collapse" /> 
  </xs:restriction>
  </xs:simpleType>
  </xs:attribute>
- <xs:attribute name="acct_gn_cd">
- <xs:annotation>
  <xs:documentation>Account Group</xs:documentation> 
  </xs:annotation>
- <xs:simpleType>
- <xs:restriction base="xs:string">
  <xs:maxLength value="5" /> 
- <xs:enumeration value="CorG">
- <xs:annotation>
  <xs:documentation>CorG - Core G</xs:documentation> 
  </xs:annotation>
  </xs:enumeration>
- <xs:enumeration value="CloG">
- <xs:annotation>
  <xs:documentation>CloG - Closed G</xs:documentation> 
  </xs:annotation>
  </xs:enumeration>
- <xs:enumeration value="GT5">
- <xs:annotation>
  <xs:documentation>GT5 - G Type 5</xs:documentation> 
  </xs:annotation>
  </xs:enumeration>
- <xs:enumeration value="Other">
- <xs:annotation>
  <xs:documentation>Other - Other</xs:documentation> 
  </xs:annotation>
  </xs:enumeration>
- <xs:enumeration value="UA&L">
- <xs:annotation>
  <xs:documentation>UA&L - Unblocked A & L</xs:documentation> 
  </xs:annotation>
  </xs:enumeration>
  </xs:restriction>
  </xs:simpleType>
  </xs:attribute>
- <xs:attribute name="acct_rcncl_nm_cd" use="required">
- <xs:annotation>
  <xs:documentation>Account Reconciliation Name</xs:documentation> 
  </xs:annotation>
- <xs:simpleType>
- <xs:restriction base="xs:string">
  <xs:maxLength value="5" /> 
- <xs:enumeration value="0">
- <xs:annotation>
  <xs:documentation>Adjustment #1</xs:documentation> 
  </xs:annotation>
  </xs:enumeration>
- <xs:enumeration value="1">
- <xs:annotation>
  <xs:documentation>Cash Difference</xs:documentation> 
  </xs:annotation>
  </xs:enumeration>
- <xs:enumeration value="2">
- <xs:annotation>
  <xs:documentation>Uncollected SDB</xs:documentation> 
  </xs:annotation>
  </xs:enumeration>
- <xs:enumeration value="3">
- <xs:annotation>
  <xs:documentation>Unblocked A&L</xs:documentation> 
  </xs:annotation>
  </xs:enumeration>
- <xs:enumeration value="4">
- <xs:annotation>
  <xs:documentation>Closed G</xs:documentation> 
  </xs:annotation>
  </xs:enumeration>
- <xs:enumeration value="5">
- <xs:annotation>
  <xs:documentation>Not Applicable</xs:documentation> 
  </xs:annotation>
  </xs:enumeration>
  </xs:restriction>
  </xs:simpleType>
  </xs:attribute>
- <xs:attribute name="gnrc_set_nm_cd" use="required">
- <xs:annotation>
  <xs:documentation>Generic Set Name</xs:documentation> 
  </xs:annotation>
- <xs:simpleType>
- <xs:restriction base="xs:string">
  <xs:maxLength value="5" /> 
- <xs:enumeration value="1">
- <xs:annotation>
  <xs:documentation>UNPOSTABLE-G</xs:documentation> 
  </xs:annotation>
  </xs:enumeration>
- <xs:enumeration value="2">
- <xs:annotation>
  <xs:documentation>PROCESSING-DIFF</xs:documentation> 
  </xs:annotation>
  </xs:enumeration>
- <xs:enumeration value="3">
- <xs:annotation>
  <xs:documentation>UNPOST-ITEM</xs:documentation> 
  </xs:annotation>
  </xs:enumeration>
- <xs:enumeration value="4">
- <xs:annotation>
  <xs:documentation>UNPOST-DELAYED</xs:documentation> 
  </xs:annotation>
  </xs:enumeration>
- <xs:enumeration value="5">
- <xs:annotation>
  <xs:documentation>RT-BK-BOX-UNPOST-ITEM</xs:documentation> 
  </xs:annotation>
  </xs:enumeration>
- <xs:enumeration value="6">
- <xs:annotation>
  <xs:documentation>RT-RSP-OUT-OF-BAL</xs:documentation> 
  </xs:annotation>
  </xs:enumeration>
- <xs:enumeration value="7">
- <xs:annotation>
  <xs:documentation>3RD-PARTY-MFDS-TRSF</xs:documentation> 
  </xs:annotation>
  </xs:enumeration>
- <xs:enumeration value="8">
- <xs:annotation>
  <xs:documentation>RRIF-MF/TD-SUSP</xs:documentation> 
  </xs:annotation>
  </xs:enumeration>
  </xs:restriction>
  </xs:simpleType>
  </xs:attribute>
- <xs:attribute name="ublk_al_act_gr_ind" use="required">
- <xs:annotation>
  <xs:documentation>Unblocked A and L Account Group Ind.</xs:documentation> 
  </xs:annotation>
- <xs:simpleType>
- <xs:restriction base="xs:string">
  <xs:maxLength value="1" /> 
- <xs:enumeration value="0">
- <xs:annotation>
  <xs:documentation>0 - Yes</xs:documentation> 
  </xs:annotation>
  </xs:enumeration>
- <xs:enumeration value="1">
- <xs:annotation>
  <xs:documentation>1 - No</xs:documentation> 
  </xs:annotation>
  </xs:enumeration>
  </xs:restriction>
  </xs:simpleType>
  </xs:attribute>
- <xs:attribute name="mn_ac_g_ery_rw_ind" use="required">
- <xs:annotation>
  <xs:documentation>E Manual Entry Review Account Group Ind.</xs:documentation> 
  </xs:annotation>
- <xs:simpleType>
- <xs:restriction base="xs:string">
  <xs:maxLength value="1" /> 
- <xs:enumeration value="0">
- <xs:annotation>
  <xs:documentation>0 - Yes</xs:documentation> 
  </xs:annotation>
  </xs:enumeration>
- <xs:enumeration value="1">
- <xs:annotation>
  <xs:documentation>1 - No</xs:documentation> 
  </xs:annotation>
  </xs:enumeration>
  </xs:restriction>
  </xs:simpleType>
  </xs:attribute>
- <xs:attribute name="man_acct_grp_ind" use="required">
- <xs:annotation>
  <xs:documentation>Account Manual Group Ind.</xs:documentation> 
  </xs:annotation>
- <xs:simpleType>
- <xs:restriction base="xs:string">
  <xs:maxLength value="1" /> 
- <xs:enumeration value="0">
- <xs:annotation>
  <xs:documentation>0 - Yes</xs:documentation> 
  </xs:annotation>
  </xs:enumeration>
- <xs:enumeration value="1">
- <xs:annotation>
  <xs:documentation>1 - No</xs:documentation> 
  </xs:annotation>
  </xs:enumeration>
  </xs:restriction>
  </xs:simpleType>
  </xs:attribute>
- <xs:attribute name="rcncl_typ_cd" use="required">
- <xs:annotation>
  <xs:documentation>E G Account Reconciliation Type</xs:documentation> 
  </xs:annotation>
- <xs:simpleType>
- <xs:restriction base="xs:string">
  <xs:maxLength value="1" /> 
- <xs:enumeration value="M">
- <xs:annotation>
  <xs:documentation>M - Monitor</xs:documentation> 
  </xs:annotation>
  </xs:enumeration>
- <xs:enumeration value="R">
- <xs:annotation>
  <xs:documentation>R - Reconciliation</xs:documentation> 
  </xs:annotation>
  </xs:enumeration>
- <xs:enumeration value="B">
- <xs:annotation>
  <xs:documentation>B - Both</xs:documentation> 
  </xs:annotation>
  </xs:enumeration>
  </xs:restriction>
  </xs:simpleType>
  </xs:attribute>
- <xs:attribute name="rel_sop">
- <xs:annotation>
  <xs:documentation>E Related SOP</xs:documentation> 
  </xs:annotation>
- <xs:simpleType>
- <xs:restriction base="xs:string">
  <xs:maxLength value="100" /> 
  <xs:whiteSpace value="collapse" /> 
  </xs:restriction>
  </xs:simpleType>
  </xs:attribute>
- <xs:attribute name="acct_resp_ind" use="required">
- <xs:annotation>
  <xs:documentation>E G Account Responsibility Ind.</xs:documentation> 
  </xs:annotation>
- <xs:simpleType>
- <xs:restriction base="xs:string">
  <xs:maxLength value="1" /> 
- <xs:enumeration value="0">
- <xs:annotation>
  <xs:documentation>0 - Yes</xs:documentation> 
  </xs:annotation>
  </xs:enumeration>
- <xs:enumeration value="1">
- <xs:annotation>
  <xs:documentation>1 - No</xs:documentation> 
  </xs:annotation>
  </xs:enumeration>
  </xs:restriction>
  </xs:simpleType>
  </xs:attribute>
- <xs:attribute name="acct_resp_strt_dt">
- <xs:annotation>
  <xs:documentation>Responsibility Start Date</xs:documentation> 
  </xs:annotation>
- <xs:simpleType>
  <xs:restriction base="xs:date" /> 
  </xs:simpleType>
  </xs:attribute>
- <xs:attribute name="acct_resp_end_dt">
- <xs:annotation>
  <xs:documentation>Responsibility End Date</xs:documentation> 
  </xs:annotation>
- <xs:simpleType>
  <xs:restriction base="xs:date" /> 
  </xs:simpleType>
  </xs:attribute>
- <xs:attribute name="E_cmt">
- <xs:annotation>
  <xs:documentation>E Comments</xs:documentation> 
  </xs:annotation>
- <xs:simpleType>
- <xs:restriction base="xs:string">
  <xs:maxLength value="600" /> 
  <xs:whiteSpace value="collapse" /> 
  </xs:restriction>
  </xs:simpleType>
  </xs:attribute>
- <xs:attribute name="sts_cd" use="required">
- <xs:annotation>
  <xs:documentation>Status Code</xs:documentation> 
  </xs:annotation>
- <xs:simpleType>
- <xs:restriction base="xs:string">
  <xs:maxLength value="1" /> 
- <xs:enumeration value="O">
- <xs:annotation>
  <xs:documentation>O - Open</xs:documentation> 
  </xs:annotation>
  </xs:enumeration>
- <xs:enumeration value="C">
- <xs:annotation>
  <xs:documentation>C - Closed</xs:documentation> 
  </xs:annotation>
  </xs:enumeration>
  </xs:restriction>
  </xs:simpleType>
  </xs:attribute>
  </xs:complexType>
  </xs:element>
  </xs:sequence>
  </xs:complexType>
- <xs:key name="acct_key">
  <xs:selector xpath="acct" /> 
  <xs:field xpath="@acct_no" /> 
  </xs:key>
  </xs:element>
  </xs:schema>

karthikr25 · Post by **karthikr25** » Tue Apr 30, 2013 10:55 am

From the above sample taking a record for example

XML retrieve response record:
<acct acct_gn_cd="0" acct_rcncl_nm_cd="3" acct_resp_ind="0" acct_resp_strt_dt="2013-04-10" acct_no="0000000" gnrc_set_nm_cd="2" man_acct_grp_ind="1" mn_ac_g_ery_rw_ind="1" rcncl_typ_cd="R" sts_cd="O" ublk_al_act_gr_ind="0"/>

Using Datastage stages and xsd would like to convert the xml to Flat file ( which includes metadata info)

acct_gn_cd="0"
acct_rcncl_nm_cd="Unblocked A&L"
acct_resp_ind="Yes"
acct_resp_strt_dt="2013-04-10"
acct_no="0000000"
gnrc_set_nm_cd="PROCESSING-DIFF"
man_acct_grp_ind="1 - No"
mn_ac_g_ery_rw_ind="1 - No"
rcncl_typ_cd="R - Reconciliation"
sts_cd="O - Open"
ublk_al_act_gr_ind="0 - Yes"

rameshrr3 · Post by **rameshrr3** » Tue Apr 30, 2013 12:02 pm

Hi There , you cannot use XML input stage for this in my opinion - especially if attribute names can have many other 'undefined' values.

You would be better off defining a small xslt and use an XML transformer stage.

Try This

Code: Select all

<xsl:stylesheet version="1.0"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output method="text"/>

  <xsl:template match="/*">
      <xsl:for-each select="@*">
        <xsl:value-of select="concat(name(.),'=',.,' ')"/>
      </xsl:for-each>
    </xsl:template>
</xsl:stylesheet>

eostic · Post by **eostic** » Tue Apr 30, 2013 1:18 pm

Regardless of how it's done (via xml stages or via xslt), the solution entails parsing the xsd as "actual data" and then providing that information via (probably) lookup, as Craig noted way up above in this thread.

There isn't anything automatic that is going to apply the details in the xsd, especially since those details are merely "documentation".

If done creatively, it could be done in the same job, and fed to the lookup at run time.....but if the xsd is particularly static, you could just do it once and put the results in a file for loading in each Job run.

Set aside the overall goal for the moment and be sure you are able to successfully parse and read the xsd "as though it was data". It's just xml itself. You could use the xmlInput Stage, the xml Stage, or as suggested, if you are comfortable with it, the xml Transformer Stage with xslt. I'd probably use the xmlInput Stage if for no other reason than it's more likely that another developer who comes along will be able to read it and use it. ...but if you have xslt expertise, or someone on your team does, it is also a very effective solution.

Once you can get the codes and documentation lines extracted into a table or at least into an output link, the rest of the solution will fall into place.

Ernie

rameshrr3 · Post by **rameshrr3** » Tue Apr 30, 2013 2:06 pm

doing it with xslt will not require a lookup to happen - unless 2 different xmls are being combined on element or attribute data( liketeh first couple of possts by OP) . If an XML transformer is used , even an xsd is not absolutely necessary if the later posted example by the OP is what they are looking for.