Four Stars

How to merge multiple xml rows ?

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|

 

  • Data Integration
1 ACCEPTED SOLUTION

Accepted Solutions
Seven Stars

Re: How to merge multiple xml rows ?

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

 

First - parce XML File (tFileInputXML)

Second - tPivotToColumnDelimited

 

 

Screen Shot 2017-04-19 at 11.58.49 AM.pngScreen Shot 2017-04-19 at 11.58.57 AM.png

than You receive expected result

 

id;name;1001;1002;1003;1004
1234;Cake;Regular;Chocolate;Blueberry;
5678;Raised;Regular;Chocolate;Blueberry;Raspberry
-----------
2 REPLIES
Moderator

Re: How to merge multiple xml rows ?

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

Seven Stars

Re: How to merge multiple xml rows ?

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

 

First - parce XML File (tFileInputXML)

Second - tPivotToColumnDelimited

 

 

Screen Shot 2017-04-19 at 11.58.49 AM.pngScreen Shot 2017-04-19 at 11.58.57 AM.png

than You receive expected result

 

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