Transform XML file to CSV using Talend Open Studio

I have worked with several enterprise ETL (Extract Transform and Load) tools like Abinitio and Datastage. Recently I have started learning open source tool named Talend. Talend is leading open source tool which is mainly used in following areas:

1. Data Integration.
2. Data Quality
3. Master Data Management
4. Business Process Management

You can find more information on  www.talend.com.

Today I have created a job to read XML file having employee data and transform it to CSV.

Lets start with creating metadata for Input XML file. We have Input file having employees data. Root tag of the XML file is Employees and for every record it contains repeating Employee child tags. 

Input XML File:

Follow the steps mentioned below to create Metadata for the above input XML file:

1. Right click the "File XML" under the metadata tab in the Repository pane. Click "Create File XML" option.
2. Enter the name as xmltocsv_metadata in the dialog box and click Next.
3. In the Step2 select InputXML radio box and click Next.
4. In Step 3, browse the sample input XML file (shown above) and select the Encoding as UTF-8.Now you can see the relation of the XML tags in the schema viewer.
5. Drag Employee tag to XPath loop expression and  Drag all the required tags to the "Feilds to Extract" section. Click on Refresh Preview to see the data in Tabular format. (refer screenshot below)
6. Click Next and Modify the Column defination as per requirements and click Finish.

Now we have successfully created metadata for the Employees XML input file. Drag the xmltocsv_metadata we have created to Job designer. Talend will provide the list of components we can use with the required metadata. Select tFileInputXML component. 

Drag tFileOutputDelimited component to the Job designer. Right Click tFileInputXML component and select Row--> Main and drag the link to tFileOutputDelimited component.

Now we have completed out Job. Click on Run and Check the output. For your reference, I have provided the output as below:
  

Output CSV File:

101,Vikram,Arora,M,10,45000
102,Poonam,Arora,F,11,55000
103,Puneet,Arora,M,10,35000
104,Mayank,Arora,M,12,15000
105,Tirath,Arora,M,12,60000
104,Sunita,Arora,F,10,65000


We can make this Job little more complex by adding some transformation mapping rules. As an excercise, You guys can make the Talend Job based on following mapping transformations. You will have to use tMap component to apply these transformations. Please let me know, if you face any issues in developing the Job.


Mapping Requirements

Sr. No.	Input Field	Transformation	CSV Output field
1	id	direct mapping	id
2	first_name	first_name + " " + last_name.	name
3	last_name
4	sex	If input is "M" then "Male" else if input value is "F" then "Female"	sex
5	department	must be left paded with 0 and should be displayed as an attribute for output XML	department
6	salary	direct mapping	salary

You may also like to read..