How to merge multiple xml rows ?

[Anonymous]

Hi,

I have a xml file as below:

<?xml version="1.0" encoding="UTF-8"?>
<items>
	<item id="1234" type="donut">
		<name>Cake</name>
		<ppu>0.55</ppu>
		<ListOfIngredients>
		<batters>
			<batter id="1001">Regular</batter>
			<batter id="1002">Chocolate</batter>
			<batter id="1003">Blueberry</batter>
		</batters>		
		</ListOfIngredients>
	</item>
	<item id="5678" type="donut">
		<name>Raised</name>
		<ppu>0.55</ppu>
		<ListOfIngredients>
		<batters>
			<batter id="1001">Regular</batter>
			<batter id="1002">Chocolate</batter>
			<batter id="1003">Blueberry</batter>
                        <batter id='1004">Raspberry</batter>
		</batters>
		</ListOfIngredients>
	</item>
</items>

How to read above xml and file output as below:

ItemID|Name|1001|1002|1003|1004|

1234|Cake|Regular|Chocolate|Blueberry||

5678|Raised|Regular|Chocolate|Blueberry|Raspberry|

[Anonymous]

Hello,

Here is a tExtractXMLField component which reads an input XML field of a file or a database table and extracts desired data.

https://help.talend.com/display/TalendComponentsReferenceGuide63EN/tExtractXMLField

Best regards

Sabrina

[vapukov · Accepted solution]

generally You need 2 component, 1 provided by Talend and 1 from provided by Community (Talend Exchange)

First - parce XML File (tFileInputXML)

Second - tPivotToColumnDelimited

than You receive expected result

id;name;1001;1002;1003;1004
1234;Cake;Regular;Chocolate;Blueberry;
5678;Raised;Regular;Chocolate;Blueberry;Raspberry