Mapping multiple tabular columns (source) to a xml loop element

One Star

Mapping multiple tabular columns (source) to a xml loop element

Hi Team:
I have a requirement to extract data from database and export to xml. The tricky part is each column name should go to a loop element's attribute in an xml.
Example :
Input : 
----------
Database columns :
==================
NAME,SEX,AGE,COUNTRY
GEORGE,M,61,USA
CHING,M,45,CHINA
==================
Output : 
--------
XML as below
<Employees>
<Employee>
<Description type="NAME">George</Description>
<Description type="SEX">M</Description>
<Description type="AGE">61</Description>
<Description type="COUNTRY">USA</Description>
</Employee>
<Employee>
<Description type="NAME">CHING</Description>
<Description type="SEX">M</Description>
<Description type="AGE">45</Description>
<Description type="COUNTRY">CHINA</Description>
</Employee>
</Employees>
Key points:
1. Based on each record in DB table ,an Employee entity has to be generated in xml.
2. DB Column name should go to xml Description's attribute "type". Description is a loop element.
Please advice how to proceed.
 
Community Manager

Re: Mapping multiple tabular columns (source) to a xml loop element

Hi 
You need to iterate each record and append it to existing XML file. The job looks like:
father job:
tMysqlInput--main--tFlowToIterate--iterate--tRunJob
trunJob: pass current record to child job, and call the child job
child job:
tFixedFlowInput--main--tNormazliedRow--main--tMap--main--tAdvancedFileOutputXML
on tFixedFlowInput: generate the current record passed from father job.
on tNormalizedRow: normalized one row to multiple rows. for example:
GEORGE,M,61,USA

becomes:

GEORGE
M
61
USA

on tMap: add a sequence number for each line, add a new column called type in the output table and set its value based the line number, for exmaple:
Var.line%4==0?"COUNTRY": (Var.line%4==3?"AGE": (Var.line%4==2?"SEX":"NAME"))

tAdvancedFileOutputXML: generate the XML file if current record is the first line, otherwise, append current record to an existing XML file. see
https://help.talend.com/search/all?query=Append+the+source+xml+file+feature&content-lang=en
BR
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Mapping multiple tabular columns (source) to a xml loop element

Thanks for your reply Shong!
On top of the above requirement, there are millions of (DB)records of that kind of structure.
What would be the fastest approach ? 
I tried something like tMySqlInput ->main -> tAdvanceOutputXml(some intermediate xml format based on db columns as elements) then applied tXsl to get the desired format.
But it takes 2 hours of times to complete the job for completing the 300 million records.
I am looking for a better solution. Please guide me.

Calling Talend Open Studio Users

The first 100 community members completing the Open Studio survey win a $10 gift voucher.

Start the survey

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

6 Ways to Start Utilizing Machine Learning with Amazon We Services and Talend

Look at6 ways to start utilizing Machine Learning with Amazon We Services and Talend

Blog