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

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

dataleadge — Tue, 13 Aug 2019 08:06:50 GMT

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

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

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

manodwhb — Tue, 13 Aug 2019 12:33:19 GMT

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

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

iamabhishek — Tue, 13 Aug 2019 12:38:30 GMT

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>

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

dataleadge — Tue, 13 Aug 2019 13:05:32 GMT

Hi,

I have attached and shared the job design files.

tfileinputxml.png
tfileinputxml2.png
Xpath.png

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

manodwhb — Tue, 13 Aug 2019 13:38:21 GMT

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

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

tnewbie — Tue, 13 Aug 2019 15:29:24 GMT

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

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

dataleadge — Wed, 14 Aug 2019 07:51:25 GMT

It worked!!!

Thanks a lot!

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

manodwhb — Wed, 14 Aug 2019 10:13:30 GMT

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