XML Repetitive Elements

[koolsun85]

I have been going through the XML posts here, but not able to find an appropriate solution.

Here is my issue.
I have a Material(Parent) and its corresponding Plants, Sales Orgs, likewise 3 more children for every material.

The business requirement is that an MQ Message should contain a Material and all its children in single message. As of now, the job is working and sending multiple messages.

Here is the Job Design:

Dataset-->Lkp-->RemoveDups-->Transformer-->XML-->MQ

As per the XML Guide I mentioned the Child IDs as Keys and removed Material as Key in the XML OutPut stage.

Here is the sample XSD.

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="MATERIAL" type="MaterialType"/>
<xsd:complexType name="MaterialType">
<xsd:sequence>
<xsd:element name="MATERIAL_NO" type="xsd:string"/>
<xsd:element name="FIELD1" type="xsd:boolean" minOccurs="0"/>
<xsd:element name="FIELD2" type="xsd:string" minOccurs="0"/>
<xsd:element name="FIELD3" type="xsd:double" minOccurs="0"/>
<xsd:element name="FIELD4" type="xsd:dateTime" minOccurs="0"/>

</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="MaterialAlternateUomType">
<xsd:sequence>
<xsd:element name="ALT_UOM_CD" type="xsd:string"/>
<xsd:element name="FIELD1" type="xsd:boolean" minOccurs="0"/>
<xsd:element name="FIELD2" type="xsd:string" minOccurs="0"/>
<xsd:element name="FIELD3" type="xsd:double" minOccurs="0"/>
<xsd:element name="FIELD4" type="xsd:dateTime" minOccurs="0"/>
</xsd:sequence>
</xsd:complexType>
</xsd:schema>


But No luck so far, I even tried Parent Key as Trigger Column, with an assumption of its change will trigger a new file, but it did not work either.

Thanks for looking into this.

[chulett]

Example snippets, please - both of what XML you are getting now and what you would like to be getting.

[koolsun85]

Expected OutPut:


<defns:MATERIAL xmlns:defns="
<defns:MATERIAL_NO>000000000001234567</defns:MATERIAL_NO>
<defns:FIELD1>false</defns:FIELD1>
<defns:FIELD2/>
<defns:FIELD3>0</defns:FIELD3>
<defns:FIELD4/>
<defns:ALT_UOM>
<defns:ALT_UOM_CD>CS</defns:ALT_UOM_CD>
<defns:FIELD1>6</defns:FIELD1>
<defns:FIELD2>1</defns:FIELD2>
<defns:FIELD3>300871406025</defns:FIELD3>
<defns:FIELD4>2010-07-06T13:17:49Z</defns:FIELD4>
</defns:ALT_UOM>
<defns:ALT_UOM>
<defns:ALT_UOM_CD>EA</defns:ALT_UOM_CD>
<defns:FIELD1>6</defns:FIELD1>
<defns:FIELD2>1</defns:FIELD2>
<defns:FIELD3>300871406025</defns:FIELD3>
<defns:FIELD4>2010-07-06T13:17:49Z</defns:FIELD4>
</defns:ALT_UOM>
<defns:SALES>
<defns:MATERIAL_ACCT_DET_GRP_CD>Z1</defns:MATERIAL_ACCT_DET_GRP_CD>
<defns:FIELD1>6</defns:FIELD1>
<defns:FIELD2>1</defns:FIELD2>
<defns:FIELD3>300871406025</defns:FIELD3>
<defns:FIELD4>2010-07-06T13:17:49Z</defns:FIELD4>
</defns:SALES>
<defns:SALES>
<defns:MATERIAL_ACCT_DET_GRP_CD>Z2</defns:MATERIAL_ACCT_DET_GRP_CD>
<defns:FIELD1>6</defns:FIELD1>
<defns:FIELD2>1</defns:FIELD2>
<defns:FIELD3>300871406025</defns:FIELD3>
<defns:FIELD4>2010-07-06T13:17:49Z</defns:FIELD4>
</defns:SALES>
</defns:MATERIAL>


Actual Generating OutPut:

<defns:MATERIAL xmlns:defns="">
<defns:MATERIAL_NO>000000000001234567</defns:MATERIAL_NO>
<defns:FIELD1>false</defns:FIELD1>
<defns:FIELD2/>
<defns:FIELD3>0</defns:FIELD3>
<defns:FIELD4/>
<defns:ALT_UOM>
<defns:ALT_UOM_CD>CS</defns:ALT_UOM_CD>
<defns:FIELD1>6</defns:FIELD1>
<defns:FIELD2>1</defns:FIELD2>
<defns:FIELD3>300871406025</defns:FIELD3>
<defns:FIELD4>2010-07-06T13:17:49Z</defns:FIELD4>
</defns:ALT_UOM>
<defns:SALES>
<defns:MATERIAL_ACCT_DET_GRP_CD>Z1</defns:MATERIAL_ACCT_DET_GRP_CD>
<defns:FIELD1>6</defns:FIELD1>
<defns:FIELD2>1</defns:FIELD2>
<defns:FIELD3>300871406025</defns:FIELD3>
<defns:FIELD4>2010-07-06T13:17:49Z</defns:FIELD4>
</defns:SALES>
</defns:MATERIAL>


<defns:MATERIAL xmlns:defns="">
<defns:MATERIAL_NO>000000000001234567</defns:MATERIAL_NO>
<defns:FIELD1>false</defns:FIELD1>
<defns:FIELD2/>
<defns:FIELD3>0</defns:FIELD3>
<defns:FIELD4/>
<defns:ALT_UOM>
<defns:ALT_UOM_CD>EA</defns:ALT_UOM_CD>
<defns:FIELD1>6</defns:FIELD1>
<defns:FIELD2>1</defns:FIELD2>
<defns:FIELD3>300871406025</defns:FIELD3>
<defns:FIELD4>2010-07-06T13:17:49Z</defns:FIELD4>
</defns:ALT_UOM>
<defns:SALES>
<defns:MATERIAL_ACCT_DET_GRP_CD>Z1</defns:MATERIAL_ACCT_DET_GRP_CD>
<defns:FIELD1>6</defns:FIELD1>
<defns:FIELD2>1</defns:FIELD2>
<defns:FIELD3>300871406025</defns:FIELD3>
<defns:FIELD4>2010-07-06T13:17:49Z</defns:FIELD4>
</defns:SALES>
</defns:MATERIAL>

[eostic]

You have independent repeating child nodes. Find the best practices in the threads here.....study the techniques needed to build multiple nodes and put them together...basically, you build each one separately in their own xmlOutput Stage, then bring them together at the end in a final xmlOutput Stage.

[kduke]

If you do a dsjob -report XML project jobname then you see mutiple repeating groups. They have repeating groups for parameters and another for links and row counts.

[Kalaiselva]

Hi,

I faced the same repeating node issue. I solved it by spitting the repeating nodes into separate XML's and then merge the XML's. finally feed the merged output to parent XML stage.

Thanks
Kalai

[koolsun85]

I was able to get the file, but with lot of irregularities.
Thanks to the best practices document, at least I have file now, and will start worrying about its format in another thread.