XML Input Stage - help
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 8
- Joined: Thu Apr 18, 2013 2:35 pm
- Location: Toronto
XML Input Stage - help
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
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
karthik
-
- Participant
- Posts: 8
- Joined: Thu Apr 18, 2013 2:35 pm
- Location: Toronto
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">
- <!-- 2013-04-18t17:12:15.457 eff_dt=2013-04-18;metadata=rsaxza;provider=db2cli;dsn=***_dva;uid=*****
-->
- <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>
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">
- <!-- 2013-04-18t17:12:15.457 eff_dt=2013-04-18;metadata=rsaxza;provider=db2cli;dsn=***_dva;uid=*****
-->
- <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>
karthik
-
- Participant
- Posts: 8
- Joined: Thu Apr 18, 2013 2:35 pm
- Location: Toronto
-
- Participant
- Posts: 8
- Joined: Thu Apr 18, 2013 2:35 pm
- Location: Toronto
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>
karthik
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
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
Ernie Ostic
blogit!
<a href="https://dsrealtime.wordpress.com/2015/0 ... ere/">Open IGC is Here!</a>
blogit!
<a href="https://dsrealtime.wordpress.com/2015/0 ... ere/">Open IGC is Here!</a>
-
- Participant
- Posts: 8
- Joined: Thu Apr 18, 2013 2:35 pm
- Location: Toronto
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
....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
Ernie Ostic
blogit!
<a href="https://dsrealtime.wordpress.com/2015/0 ... ere/">Open IGC is Here!</a>
blogit!
<a href="https://dsrealtime.wordpress.com/2015/0 ... ere/">Open IGC is Here!</a>
-
- Participant
- Posts: 8
- Joined: Thu Apr 18, 2013 2:35 pm
- Location: Toronto
Ok. below is the XML and XSD
XML
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>
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>
karthik
-
- Participant
- Posts: 8
- Joined: Thu Apr 18, 2013 2:35 pm
- Location: Toronto
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)
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"
karthik
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
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>
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
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
Ernie Ostic
blogit!
<a href="https://dsrealtime.wordpress.com/2015/0 ... ere/">Open IGC is Here!</a>
blogit!
<a href="https://dsrealtime.wordpress.com/2015/0 ... ere/">Open IGC is Here!</a>
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.