sqlxml to import xml data into sqlsever using store procedure

Posted on 2006-05-06
Last Modified: 2008-01-09

How to use sqlxml to Convert XML data into relational data and load it into an existing SQL Server 2000 database using store procedure ? I do not want to use VB/.net/C# etc. I would just like to use store procedure for this.

Question by:khinvra
    LVL 75

    Accepted Solution

    Hi Rahul,
    You can use OPENXML to do this, Refer OPENXML in BOL. This is the example taken from BOL.
    If you have a different structure, then send me the xml

    DECLARE @idoc int
    DECLARE @doc varchar(1000)
    SET @doc ='
    <Customer CustomerID="VINET" ContactName="Paul Henriot">
       <Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
          <OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
          <OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
    <Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
       <Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
          <OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
    --Create an internal representation of the XML document.
    EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
    -- Execute a SELECT statement that uses the OPENXML rowset provider.
    SELECT    *
    FROM       OPENXML (@idoc, '/ROOT/Customer',1)
                WITH (CustomerID  varchar(10),
                      ContactName varchar(20))

    Aneesh R!
    LVL 75

    Assisted Solution

    by:Anthony Perkins
    The critical part that is missing from that example (and I only hope this was an oversight) was the following line at the very end:

    EXEC sp_xml_removedocument @idoc

    If you do not do that, you will eventually run out of memory.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    731 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now