XML Input Stage Repetition Element Issue

kashif007 · Post by **kashif007** » Wed Feb 08, 2012 3:48 pm

I am trying to parse an XML file using XML Input stage. It is a complex XML structure. I imported the related XSD and namespace code into the XML input stage. My problem is that I am unable to determine the Repitition key in my XML ? I thought, in order to get everything from the XML input stage, I would choose the "lowest granular repeating element" as my repition key column . But the issue is that this "lowest granular repeating element" in an XML is "Optional". Meaning the data for this column might exist or might not exist. When I choose this column as key and the data in this column on the XML does not show up, the XML does not parse any data to the output but when I use a different file or I go few granular levels above the lowest granular column, XML input stage does parsed the data.

How should I handle a scenario where your repeating element is optional ?

eostic · Post by **eostic** » Wed Feb 08, 2012 4:25 pm

There are several ways you can approach this....

The first is to try and find an element at the lowest level (the lowest level for which you want "rows" retrieved for each repeating element) that is not optional.....

...but if there isn't one, work with the setting called "Repetition Element Required". If this is checked, then any node without the Repetition Element will NOT be retrieved (nor will it's parent/grandparent/etc.). But if that box is UN-checked, you will get the parent node, and all the others above it, and a NULL value for the Repetition Element that does not exist in the current document.

Ernie

kashif007 · Post by **kashif007** » Thu Feb 09, 2012 2:51 pm

I am sorry I cannot see your answer because I don't have premium account. Following is the XPath for the lowest repeating element in my XML. The XPath highlighted in bold is the one that is optional

/RoutingMessageEnvelope/RoutingInputMessage/MessagePayload/paymentOrderRemittanceAdviceRequest/request/entities/remittances/remittanceAdjustments/adjustmentBaselineItems/allowanceOrCharge

Would it be possible for you to not give a reply using premium account ?

ray.wurlod · Post by **ray.wurlod** » Thu Feb 09, 2012 6:33 pm

Would it be possible for you to obtain a premium membership?

Premium membership is what keeps this site alive, by generating funds to pay the hosting and bandwidth costs.

Asking us to bypass that mechanism is akin to telling us you don't care about the continued survival of DSXchange.

kashif007 · Post by **kashif007** » Tue Feb 14, 2012 9:01 am

Since I am a premium member now, I add another question relating to this topic.

I was able to parse out the optional data at the lowest granular level. Now I am facing another challenge that I need to regroup the data from other granular levels into one table. Optional data is highlighted in bold in the below example.

For example:

Xpath1:

/pay:paymentOrderRemittanceAdviceRequest/pay:request/pay:transactionSummary/core:customerAccountNumber

Xpath2:

/pay:paymentOrderRemittanceAdviceRequest/pay:entities/pay:remittances/pay:remittanceAdjustments/pay:reference/pay:refNumber/pay:unencryptedReferenceNumber/pay:refNumber

Like wise I have other data present on another Xpath's. 10 different Xpath's in total. How can I group them back together after parsing, to maintain the Data integrity. I was thinking to use a composite key for each Xpath which can be use to join each set back together but that also has possibility of loosing the data integrity and creating unwanted duplicates.

Please advice

eostic · Post by **eostic** » Tue Feb 14, 2012 10:20 am

It depends....are each of those independent nodes repeating on their own? If so, then it's no different than reading multiple relational tables and making decisions on how they should be combined downstream. Grab your common "keys", whatever they are (higher level node element and attribute values that the multiple sub-nodes have in common), and decide how you want things to look....Downstream DataStage will support your ability to join them in lots of ways.

Either way, retrieve them via xmlInput according to their repeating nodes, getting multiple rows for each on their own link.......what you do with them downstream is up to you.

"if" a particular node is absolutely certain to be occurring only once...then it can generally be included on the link beneath it. For example, you might have a major node of "company" with a repeating "employee" node below it. The company node might have a "incorporation details" node above employee that is optionally populated, but if populated, never occurs more than once. You can usually include that on a lower repeating link without a problem. The values will appear, along with company info, repeated for each employee, or just be null if they don't exist.

Ernie