Retrieving XML Custom Data

[kmrprvn]

Hi  All,

I am facing problem in retrieving certain fields like Location,Status,Source from below XML.  i tried with tFileInputXML, but its fetching all attributes id including unnecessary guest,Tax data. As  Can some Please Explain how can i Extract specified attributes(order-no,Location,Status,Source) from XML file.

Sample Source File:

<orders>
<order order-no="20001">
<manuals>
<manuals state-id="Location">Sydney</manuals>
<manuals state-id="Status">Success</manuals>
<manuals state-id="guest">false</manuals>
<manuals state-id="Tax">true</manuals>
<manuals state-id="Source">Web</manuals>
</manualss>
</order>
<order order-no="20002">
<manuals>
<manuals state-id="Location">Mexico</manuals>
<manuals state-id="Status">Success</manuals>
<manuals state-id="guest">false</manuals>
<manuals state-id="Source">Mobile</manuals>
</manualss>
</order>
</orders>

Thanks in Advance.

[Anonymous · Accepted solution]

Unfortunately your XML is not formatted correctly, but I am assuming that is just a copying error. Your custom-attributes elements are not closed properly (should be "</custom-attributes>").

To solve this you will need to loop in the tFileUnputXML like this.....

"/orders/order"

....and create one column called "order" with an xpath expression like this....

"."

Then add a tExtractXMLField component and extract your attributes here. Your loop should be ....

"/"

....and your column xpaths will need to look like this.....

"//custom-attribute[@attribute-id='Location']"

Obviously change the attribute-id value according to what you want to extract.

[kmrprvn]

---

@rhall wrote:

Unfortunately your XML is not formatted correctly, but I am assuming that is just a copying error. Your custom-attributes elements are not closed properly (should be "</custom-attributes>").

To solve this you will need to loop in the tFileUnputXML like this.....

"/orders/order"

....and create one column called "order" with an xpath expression like this....

"."

Then add a tExtractXMLField component and extract your attributes here. Your loop should be ....

"/"

....and your column xpaths will need to look like this.....

"//custom-attribute[@attribute-id='Location']"

Obviously change the attribute-id value according to what you want to extract.

---

Hey Thanks for quick reply. i have corrected end tag.

I tried in the same way as you explained, but its returning null value.

[Anonymous]

Can you run it the job and take a screen shot of the job just afterwards. Also, can you show me the whole xPath config for the tExtractXMLField component? 

[kmrprvn]

Thanks alot rhall.

There is a spell mistake. its working as expected.