Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
XML is easier to pass to a sql stored procedure than a table in .Net. Refactor your proc and you will save a ton of code in .Net.
-- There are two examples on this page
-- 1st Example
begin
DECLARE @idoc INT
DECLARE @xml nvarchar(MAX)
SET @xml='<root>
<id>
19
</id>
<prod>
<productid>1</productid>
<name size="large">Adjustable Race</name>
<productnumber>AR-5381</productnumber>
</prod>
<prod>
<productid>2</productid>
<name>Bearing Ball</name>
<productnumber>BA-8327</productnumber>
</prod>
<prod>
<productid>3</productid>
<name>BB Ball Bearing</name>
<productnumber>BE-2349</productnumber>
</prod>
</root>'
IF LEFT(@xml,5) <> '<?xml'
SET @xml='<?xml version="1.0" encoding="utf-16"?>'+@xml
-- Parse the teams from the XML
EXEC sp_xml_preparedocument @idoc OUTPUT, @xml
SELECT
z.x,
z.productid,
z.name,
y.size,
y.productid
FROM
openxml(@idoc,'/root/prod',2)
WITH (
productid INT,
x INT '../id',
name VARCHAR(50)) z JOIN
openxml(@idoc,'/root/prod/name',1)
WITH (
productid INT '../productid',
SIZE VARCHAR(50)) y
ON
z.productid=y.productid
EXEC sp_xml_removedocument @idoc
end
go
--2nd Example
-- Passing a nested object
begin
DECLARE @idoc INT, @doc VARCHAR(1000);
SET @doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order OrderID="10248" CustomerID="VINET" EmployeeID="5"
OrderDate="1996-07-04T00:00:00">
<OrderDetail ProductID="11" Quantity="12"/>
<OrderDetail ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">v
<Order OrderID="10283" CustomerID="LILAS" EmployeeID="3"
OrderDate="1996-08-16T00:00:00">
<OrderDetail ProductID="72" Quantity="3"/>
</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 *
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');
end
go
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment