XML Input with 2 Repetitions

[suryadev]

Hello,

Used XML Input to parse an XML but I see that there are 2 repetition elements in the XML. How do I give two columns as keys? can anyone please suggest. thanks!

[suryadev]

Here is my xml

<Tran>
<Version>5</Version>
<SourceId>RDB</SourceId>
<TransactionId>3200000006196</TransactionId>
<UserFields>
<UserField>
<ID>User0</ID>
<Data>03</Data>
</UserField>
<UserField>
<ID>User1</ID>
<Data>01</Data>
</UserField>
</UserFields>
<Item>
<ItemId>01</ItemId>
<ItemType>97</ItemType>
</Item>
<Item>
<ItemId>02</ItemId>
<ItemType>03</ItemType>
</Item>
</Tran>

As seen in the XML there are two repetition elements. One is ID and other ItemId. My requirement is to parse the XML, HARDCODE some columns and change it back to XML file. I have done single repetition elements and it worked fine by using XML Input and XML Output stages. Please suggest how do I proceed with this kind of data. Thanks!

[chulett]

How do I give two columns as keys?

You cannot, much like Highlander "there can only be one!". Choose the lowest as the key.

[suryadev]

ok, thanks!
Now I gave ID as key because it is the lowest.

Able to parse the columns but when used XML output, it did not combine to a XML file. When opened the XML file, it did not open in Explorer.

What should I give for the UserFields and Userfield in XML OUTPUT. Usually for parent tag we give '' in the value of the column as I gave the same for TRAN and just give the names next to it before sending to XML output stage.

Thank you!

[suryadev]

I loaded the parsed XML into a file and the element I used as key gave all the values but the other one which I thought to be a repetition element came out only once like below
Version SourceId TransactionId ID Data ItemId ItemType
5 RDB 3200000006196 User0 03 01 97
5 RDB 3200000006196 User1 01 01 97

The other ItemId 02 and Itemtype 03 is not coming out after parsing, any suggestions please. Thanks!

[eostic]

Two output links......

You have two repeating groups... Imagine them as two separate relational tables or files. One with (say) 10 rows and the other with (say) 2000 rows. There is nothing in xml that is forcing those groups to be in sync.

Two links...each with their own cols down to their subnode and each with their own repeating group "key" col.

Ernie

[suryadev]

Thank you Ernie!

I gave two different outputs from XML Input stage, did the required transformations and loaded as two different XML files. They seem to be correct as they open in Explorer.

Now I have to combine them to look like my Original source XML. How can I achieve that just by joining those XML's as tables and send to XML Output stage?

[eostic]

That can be trickier.

Suggest you look thru the forum here for "XML Best Practices" and find the document that was written long ago that reviews how to do this using xmlOutput Stage. In brief, you need to first build one of the paths, park it somewhere, then look it up downstream and add it to a column at the parent level as you build the second path......and drop this "already completed column" into your xml at the parent level.

...or alternatively, if you are in 9.x and have a formal xml Schema (xsd) for this xml, consider starting to learn the xml Stage.

Ernie

[chulett]

Pretty sure Ray is hosting that document but can't confirm from work as it is blocked.

[ray.wurlod]

Yes, hosted there.

[suryadev]

Thank you!

We are using 8.7 version and I used XML Output stage earlier to generate XML file but never used it with two different keys. I will check the document and see if I can find with two keys. Thanks again!