• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 648
  • Last Modified:

sqlxml to import xml data into sqlsever using store procedure

Hi,


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.

Rahul
0
khinvra
Asked:
khinvra
2 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
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 ='
<ROOT>
<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"/>
   </Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
   <Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
      <OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
   </Order>
</Customer>
</ROOT>'
--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!
0
 
Anthony PerkinsCommented:
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.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now