Skip to Content

Import data from xml file to sql server table


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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Best Answer
    Posted on Dec 23, 2013 at 06:18 PM

    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


    Untitled.png (98.9 kB)
    Untitled.png (118.5 kB)
    Untitled.png (128.4 kB)
    Add a comment
    10|10000 characters needed characters exceeded

    • 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

  • author's profile photo Former Member
    Former Member
    Posted on Dec 23, 2013 at 02:01 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

    • 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

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.