topic Parsing complex XML with lists in Talend Studio

Parsing complex XML with lists

Anonymous — Sat, 16 Nov 2024 12:24:52 GMT

What is an efficient way to load the below XML into a database? I want to parse the xml into three tables, item/detail/tag. I believe my loop XPath Query needs to be /database/item.
I cannot figured out a way parse a list without using multiple FileInputXML. I have tried using ExtractXMLField but it throws an error, 'Error on line 3 of document : Content is not allowed in prolog. Nested exception: Content is not allowed in prolog.'
I can load the file three times, but assume this would be very inefficienct. I have seen similar problems in the forum, none appear to have solutions.

<database>
<item id="111" clientName="SB">
<details>
<detail child_id="1">
<name>Bis</name>
<amount>2</amount>
</detail>
<detail child_id="2">
<name>Asp</name>
<amount>20</amount>
</detail>
</details>
<tags>
<tag tag_id="1">
<name>test</name>
</tag>
</tags>
</item>
<item id="112" clientName="GJ">
<details>
<detail child_id="1">
<name>Lib</name>
<amount>1</amount>
</detail>
</details>
<tags>
<tag tag_id="1">
<name>test</name>
</tag>
<tag tag_id="2">
<name>anothert</name>
</tag>
</tags>
</item>
</database>

Re: Parsing complex XML with lists

Anonymous — Wed, 01 Feb 2012 02:20:35 GMT

Hi
Because you want to parse the xml into three tables, loop XPath Queries will be different.
You can use tFileInputMSXML component here.
The following images may help you.
Using three tFileInputXML components with "Multi thread execution" is another workaround.
But if the xml file is large, the job will need more memory.

Regards,
Pedro

Re: Parsing complex XML with lists

Anonymous — Wed, 01 Feb 2012 19:24:46 GMT

Thanks, this works. In case other have this problem, I also had to set generation mode in advanced options to use SAX. The DOM didn't retrieve the nested lists.
Another method we want to try for our import we is to parse the xml and loop through /database/item, retrieve the item's xml and insert the item's xml information into the database for later use. This doesn't seem to be easy to do. A work around would be to modify the produced java and run that.
- How do I make an XPath query to return a string of the xml?
- If we do need to change the produced java code, how do I make the change permanent?
Thanks again,
Kevin