Extracting data from XML file to MS SQL server in form of table.

Highlighted
Five Stars

Extracting data from XML file to MS SQL server in form of table.

Hello,

I'm new to talend and have been trying to transfer the xml data (attached file) to MS Sql.

After using the "tFileinputXml", "tmap_xml" and "tDBouput" component only one row of the file was transferred.

Please guide me to populate multiple rows iteratively else guide me on converting the xml file to excel file.

Thanks in advance Smiley Happy

 

The tags in the files are columns while the string between tags is the data to be transferred as rows. 


Accepted Solutions
Seven Stars

Re: Extracting data from XML file to MS SQL server in form of table.

From the sample xml you shared, I think the issue is with xml file, as i don't see any nesting / looping of elements. Just for your better understanding I have added an element <COMPANY> with looping to ur xml ... please look into the below image

talend_community.PNG

View solution in original post


All Replies
Forteen Stars

Re: Extracting data from XML file to MS SQL server in form of table.

@dataleadge ,can you post your job design,since you can check the get nodes in tfileinputxml to get loops.

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Nine Stars

Re: Extracting data from XML file to MS SQL server in form of table.

The XML file structure is not correct - in the sense that the fields VCHCOMPANYNAME, VCHVCHNUMBER etc.. which are repeating n times are not correctly put inside a loop or to put correctly not inside in nested elements. Hence the first lot in your case first 10 nodes are being captured and interpreted as the only row. You need the nodes to be in correct order - in the format of nested child elements.
Something like these - 

<root>
  <Participants>
    <Gender>Male</Gender>
    <Participation>
      <ParticipationType>Open</ParticipationType>
    </Participation>
  </Participants>
  <Participants>
    <Gender>Female</Gender>
    <Participation>
      <ParticipationType>Closed</ParticipationType>
    </Participation>
  </Participants>
</root>

 

Five Stars

Re: Extracting data from XML file to MS SQL server in form of table.

Hi,

 

I have attached and shared the job design files.

Forteen Stars

Re: Extracting data from XML file to MS SQL server in form of table.

@dataleadge ,your loop xpath query is wrong ,please check

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Seven Stars

Re: Extracting data from XML file to MS SQL server in form of table.

From the sample xml you shared, I think the issue is with xml file, as i don't see any nesting / looping of elements. Just for your better understanding I have added an element <COMPANY> with looping to ur xml ... please look into the below image

talend_community.PNG

View solution in original post

Five Stars

Re: Extracting data from XML file to MS SQL server in form of table.

It worked!!! 

Thanks a lot!

Forteen Stars

Re: Extracting data from XML file to MS SQL server in form of table.

@dataleadge ,Please mark the solution the one which is helped you ,so that it would be useful to others.

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.

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 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog