tsql: parse XML to generate insert statements

Basically I want to send a block开发者_高级运维 of xml as a parameter to my ms sql 2005 db, which gets parsed and inserts row data.

I've seen plenty of examples of storing xml in the database, or retrieving table data as xml, but no examples where xml is used to insert tabular data.

I have a method in c# which calls my stored proc in a loop to insert the data, but i'm hoping I can format the data into xml and insert it all in one call.

---

Take a look at this example:

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))

This would get your the results:

CustomerID ContactName          
---------- -------------------- 
VINET      Paul Henriot
LILAS      Carlos Gonzlez

If you provide your XML I can provide more info.

Shamelessly stolen from here:

http://msdn.microsoft.com/en-us/library/aa276847%28SQL.80%29.aspx

---

I've done this quite often. Beginning with SQL Server 2005 a native XML data type is provided, along with methods for querying (via XQuery). ADO.NET aslo added support with the SqlDbType.Xml enumeration and SqlXml class. See the article "XML Data Type Support in ADO.NET 2.0: Handling XML from SQL Server 2005" for several examples.