Handle multiple records in SQL server parameters

Last week one of my colleague asked me that how we can send multiple records to SQL server procedure parameters. Let us discuss the scenario:-

Application user has selected multiple product and now you want to send Customer details and corresponding order details data to SQL Server.

To resolve this Microsoft has provided multiple approaches in SQL Server like Table and XML Parameter. I read somewhere that if you want to be a architect, try to solve each software problem with xml framework.

Let us play with XML:

DECLARE @idoc int

DECLARE @doc xml

SET @doc ='

<ROOT>

<Order OrderID="10248" CustomerID="VINET" EmployeeID="5"

OrderDate="1996-07-04T00:00:00">

</Order>

</Customer>

<Order OrderID="10283" CustomerID="LILAS" EmployeeID="3"

OrderDate="1996-08-16T00:00:00">

</Order>

</Customer>

</ROOT>'

--Create an internal representation of the XML document.

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

-- SELECT stmt using OPENXML rowset provider

SELECT *

--In OPENXML the flag value is set to 1, indicating attribute-centric mapping

--In OPENXML the flags set to 2, indicating element-centric mapping

--In OPENXML the flags set to 3, indicating text-centric mapping

FROM OPENXML (@idoc, '/ROOT/Customer/Order/OrderDetail',2)

WITH (OrderID int '../@OrderID',

CustomerID varchar(10) '../@CustomerID',

OrderDate datetime '../@OrderDate',

ProdID int '@ProductID',

Qty int '@Quantity')

EXEC sp_xml_removedocument @idoc --don't forget to remove the document

The above example will give you a table resultset (OrderID,CustomerID,OrderDate,ProductID,Quantity) and you can process the resultset in desired output by using #temp table.

How it works:

The above example creates an internal representation of the XML image by using sp_xml_preparedocument.

First, sp_xml_preparedocument is called to obtain a document handle. This document handle is passed to OPENXML. A SELECT statement that uses an OPENXML rowset provider is then executed against the internal representation of the XML document.

OPENXML Parameters include the following:

An XML document handle (idoc)

An XPath expression to identify the nodes to be mapped to rows (rowpattern)

A description of the rowset to be generated

Mapping between the rowset columns and the XML nodes

Hope this artical will help those who has similar problem in sending multiple records to SQL Server. Read more on Open XML on MSDN

Reference :MSDN and SQL Server 2005 Book Online