Data Migration From XML File to Postgres Database Using Talend

In this blog post, I’m going to discuss how to migrate data which reside within an XML file to Postgres database using TALEND before that I’ll give a brief introduction about what is TALEND.

What is TALEND?

Talend is a next-generation leader in cloud and big data integration software that helps companies become data driven by making data more accessible, improving its quality and quickly moving it where it’s needed for real-time decision making. Talend’s open-source, native, and unified integration platform, Data Fabric, enables customers to embrace new innovations and scale to meet the evolving data demands of the business.

Ok let’s move on to the tutorial I assume that you have pre-installed the TALEND OPEN STUDIO community edition or you can find installation guide from the following link

   http://www.talendforge.org/wiki/doku.php?id=doc:installation_guide

Once you setup the environment first thing we need to to is to create a project as below

By clicking the Create a new project button you can create a project with the name you desired for the project

Next, we need to create a “JOB” for that you need to follow the following steps.

  1. Click, JOB DESIGN from the left panel
  2. Then, right click on the standard link and create a “JOB” with a specific name and the purpose.

Now we have all set up to implement our migration, next we need to import XML file to extract the data, for this we need to follow following steps

Once you created the XML input it’ll appear as following and you can drag and drop that to the JOB WORKPLACE and create tFileInputXML component as below.

Next, we need to map our extracted data into the Postgres schema for that we can use tXMLMap component by typing the name on the “JOB WORKPLACE” as follow.

Then right click the Payroll_XML component and select main from the row and connect to tXMLMap_1 then you will see as follow.

Next, You need to map extracted fields from the XML to Database Schema by following below steps First, Double click tXMLMap_1 and then you will see the following screen

Then, You need to click the “Auto Map” button as per shown in Figure 14. Then you will see a mapped connection between XML fields and the database fields as follow

As a Final Step next we need setup the Postgres connection, For that first, you need to create a component tPostgresqlOutput by typing the name on the JOB WORKPLACE as below.

and we need to setup the Postgres connection details as following by double clicking the tPostgresqlOutput_1 component and then we need to connect the tXMLMap_1 and the tPostgresqlOutput_1 as follow

and our final output of the “JOB” file will be like this

Then we can run the job file and check whether our migration is working or not if every thing went well we can see the follow output.

and on the postgres database you can see the data is imported as per the XML doccument.

and that’s it like this you can migrate data from xml to postgres, following video is showing a demo for migrating a complex xml which are having nested data to postgres database.