cancel
Showing results for 
Search instead for 
Did you mean: 

Import data from xml file to sql server table

former_member214617
Participant
0 Kudos


Hi guys

I need help how to import data from xml file to sql server table... i have tried generating .XSD file from .XML file but becasue of huge XML file XSD is not getting generated... i know to import data using XSD file but is there any other way of doing the same..

can anyone help me how to do the same.

Regards

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi,

first you have to observe which root node is repeating in your big xml file.

     if you observe below xml file ,<purchaseOrder> node is repeating which is root node for remaining nodes like sallerid,sellername etc,

<?xml version="1.0" encoding="UTF-8"?>

<Query>

  <purchaseOrder>

    <sellerParty>

      <sellerid>1</sellerid>

      <sellername>raj</sellername>

    </sellerParty>

    <buyerParty>

      <buyerId>100</buyerId>

      <buyername>kumar</buyername>

    </buyerParty>

    <orderLine>

      <item>

        <name>pen</name>

        <quantity>200</quantity>

        <unitprice>10</unitprice>

        <courrency>INR</courrency>

      </item>

    </orderLine>

  </purchaseOrder>

  <purchaseOrder>

    <sellerParty>

      <sellerid>2</sellerid>

      <sellername>ravi</sellername>

    </sellerParty>

    <buyerParty>

      <buyerId>200</buyerId>

      <buyername>kumar</buyername>

    </buyerParty>

    <orderLine>

      <item>

        <name>cap</name>

        <quantity>200</quantity>

        <unitprice>10</unitprice>

        <courrency>INR</courrency>

      </item>

    </orderLine>

  </purchaseOrder>

  <purchaseOrder>

    <sellerParty>

      <sellerid>3</sellerid>

      <sellername>ramu</sellername>

    </sellerParty>

    <buyerParty>

      <buyerId>100</buyerId>

      <buyername>kumar</buyername>

    </buyerParty>

    <orderLine>

      <item>

        <name>pot</name>

        <quantity>50</quantity>

        <unitprice>100</unitprice>

        <courrency>INR</courrency>

      </item>

    </orderLine>

  </purchaseOrder>

</Query>

first you have to observe which root node is repeating and take two repenting nodes to generate xsd or dtd 

Note : if you have already installed sql server ,there will be a xml schema explorer by using that you can create xml schema as below or if you have any other tools go with that.

go to xml tab and create xsd as below

former_member214617
Participant
0 Kudos

Hi

Thanks for your help... now i am able to generate XSD file... when i am trying to create new XML Schemas under format tab then it is giving me error "cannot parse metada"

can you tell me the steps to do the same... it may be that i am skipping basic step..

Regards

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi Leo ,

you can try XML2CSV Spreadsheet Converter that can be downloaded from here.

The utility would generate a csv file for the xml and we can  import the csv data easily into sap Bods and in end to the sql server table.

former_member214617
Participant
0 Kudos

Hi Sandeep

thanks for reply... i have tried downloading the converter but it is not working as xml file is huge (217mb)... is there anyway of doing the same... i have tried googling as well for another tools but with no success

regards