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

[dataleadge]

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. 

[manodwhb]

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

[iamabhishek]

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>

[dataleadge]

Hi,

I have attached and shared the job design files.

tfileinputxml.png
tfileinputxml2.png
Xpath.png

[manodwhb]

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

[tnewbie · Accepted solution]

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

[dataleadge]

It worked!!! 

Thanks a lot!

[manodwhb]

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