topic Re: Mapping multiple tabular columns (source) to a xml loop element in Talend Studio

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

Anonymous — Sat, 28 Feb 2015 00:27:30 GMT

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.

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

Anonymous — Sat, 28 Feb 2015 01:58:39 GMT

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

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

Anonymous — Sat, 28 Feb 2015 14:20:56 GMT

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.