How to merge multiple xml rows ?

Highlighted
Five 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|

 


Accepted Solutions
Forteen 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
-----------

All 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

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Forteen 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
-----------

What’s New for Talend Spring ’19

Watch the recorded webinar!

Watch Now

Agile Data lakes & Analytics

Accelerate your data lake projects with an agile approach

Watch

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.

Download

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch