Iterating XML in SQL Server 2005 (or 2008) stored procedure

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Carl Ganz - 20 Jul 2008 23:11 GMT

I need to update add rows in a table from the UI. Rather that call my
AddData stored proc 3 times to save 3 rows of data, I'd rather wrap
the data in XML and pass this to the stored proc. Then, I'd like to
iterate the XML block and perform 3 INSERTs. How can this be
accomplished? Does anyone have a sample stored proc they could post.

Thanks

Carl

Reply to this Message

Plamen Ratchev - 21 Jul 2008 01:39 GMT

Take a look at Erland Sommarskog's article on sharing data. While targeting
sharing between stored procedures, the methods to pass XML or table valued
parameter are the same when invoked from a client API.

Passing as XML (SQL Server 2005):
http://www.sommarskog.se/share_data.html#XML

Passing as table valued parameter (SQL Server 2008):
http://www.sommarskog.se/share_data.html#tableparam

Using a table valued parameter will be the easiest way if on SQL Server 2008
(for example, in .NET you can directly pass a DataTable).

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Reply to this Message

Erland Sommarskog - 21 Jul 2008 10:09 GMT

> I need to update add rows in a table from the UI. Rather that call my
> AddData stored proc 3 times to save 3 rows of data, I'd rather wrap
> the data in XML and pass this to the stored proc. Then, I'd like to
> iterate the XML block and perform 3 INSERTs. How can this be
> accomplished? Does anyone have a sample stored proc they could post.

Here is a sample:

DECLARE @x xml
SELECT @x =
N'<Orders>
<Order OrderID="13000" CustomerID="ALFKI"
OrderDate="2006-09-20Z" EmployeeID="2">
<OrderDetails ProductID="76" Price="123" Qty = "10"/>
<OrderDetails ProductID="16" Price="3.23" Qty = "20"/>
</Order>
<Order OrderID="13001" CustomerID="VINET"
OrderDate="2006-09-20Z" EmployeeID="1">
<OrderDetails ProductID="12" Price="12.23" Qty = "1"/>
</Order>
</Orders>'
SELECT OrderID = T.Item.value('@OrderID', 'int'),
CustomerID = T.Item.value('@CustomerID', 'nchar(5)'),
OrderDate = T.Item.value('@OrderDate', 'datetime'),
EmployeeId = T.Item.value('@EmployeeID', 'smallint')
FROM @x.nodes('Orders/Order') AS T(Item)

SELECT OrderID = T.Item.value('../@OrderID', 'int'),
ProductID = T.Item.value('@ProductID', 'smallint'),
Price = T.Item.value('@Price', 'decimal(10,2)'),
Qty = T.Item.value('@Qty', 'int')
FROM @x.nodes('Orders/Order/OrderDetails') AS T(Item)

Note that you should not iterate, but you grab it all in one go.

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Reply to this Message

Carl Ganz - 22 Jul 2008 16:59 GMT

> CarlGanz (seton.softw...@verizon.net) writes:
> > I need to update add rows in a table from the UI. Rather that call my
[quoted text clipped - 37 lines]
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Thanks to everyone for their assistance with this. I think I'm much
closer to the solution but am stuck on some syntax. I'd like to take
the following XML:

<Data>
<Patient>
<LastName>Smith</LastName>
<DOB>3/12/1950</DOB>
</Patient>
<Patient>
<LastName>Jones</LastName>
<DOB>12/19/1967</DOB>
</Patient>
</Data>

and send it to this stroed proc:
ALTER PROCEDURE dbo.spc_upd_Patient

@PatientXML xml

AS

DECLARE @Patients TABLE (LastName int, DOB datetime)

INSERT INTO @Patients (LastName, DOB)
SELECT ParamValues.LastName.value('.','VARCHAR(20)'),
ParamValues.DOB.value('.','datetime')
FROM @PatientXML.nodes('/Patient/LastName') as ParamValues(LastName),
@PatientXML.nodes('/Patient/DOB') as ParamValues(DOB)

I'm not sure of the proper syntax to get it into my in-memory table,
from which I'll insert/update the main table as appropriate. What am I
doing wrong here?

Thanks

Carl

Reply to this Message

Plamen Ratchev - 22 Jul 2008 17:15 GMT

Try this:

CREATE PROCEDURE spc_upd_Patient

@PatientXML xml

AS

DECLARE @Patients TABLE
(LastName VARCHAR(20), DOB DATETIME);

INSERT INTO @Patients (LastName, DOB)
SELECT T.ParamValues.query('LastName').value('.','VARCHAR(20)'),
T.ParamValues.query('DOB').value('.','DATETIME')
FROM @PatientXML.nodes('/Data/Patient') as T(ParamValues);

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Reply to this Message

Erland Sommarskog - 22 Jul 2008 23:10 GMT

> Thanks to everyone for their assistance with this. I think I'm much
> closer to the solution but am stuck on some syntax. I'd like to take
[quoted text clipped - 10 lines]
> </Patient>
></Data>

Didn't you say that you were generating the XML in the client? In
such case, you should geneate it as:

<Data>
<Patient LastName="Smith" DOB="1950-12-03Z" />
<Patient <LastName="Jones" DOB="1967-19-12Z" />
</Data>

That is you should use attribute-centred XML and not element-centred
XML. The methods to shred an XML document in SQL Server works better
with attribute-centred XML. And as you can see, attribute-centred XML
is also more compact.

Note also that for the date, you should use date format that is
standard for XML, which means that it should either end in Z as above,
or use T between the date and time part. And the format should be
YYYY-MM-DD and nothing else.

But this is not really anything you should have to bother with. I get
a suspicion that you crafted the XML string by hand in your code,
but you should use APIs to build your XML. If you build your XML
strings your self, you will need to cater for all special characters
that needs escaping in XML. And things like date formats.

One you have your attribute-centred XML in place, my example should
get you going.

> I'm not sure of the proper syntax to get it into my in-memory table,

And, oh, there is no such things as an in-memory table. A table
variable is almost like any other table. It does have some special
properties, but it lives in tempdb and it can definitely spill to disk
if needed.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Reply to this Message

Carl Ganz - 23 Jul 2008 19:11 GMT

> CarlGanz (seton.softw...@verizon.net) writes:
> > Thanks to everyone for their assistance with this. I think I'm much
[quoted text clipped - 51 lines]
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Thanks to everyone for the guidance and advice. I'm simply protoyping
a solution that I'm planning to implement so nothing is cast in stone
yet. I want to make sure I've got the corrrect approach before moving
forward. As I tell my clients, "Making it work is not good enough!"

Thanks again

Carl

Reply to this Message

sloan - 21 Jul 2008 16:32 GMT

If you take the time to wrap it up into xml, you DO NOT WANT TO "iterate"
and insert / update 3 times.

Add or Update all your records in one hit.

Use the syntax from previous posts..

You can either throw the values into a #temp or @variable table and then use
them.
Or "go straight".

I like the #temp or @variable table approach because I can debug a little
easier.

YOu can look here:
http://support.microsoft.com/kb/315968
but if you're at Sql2005 or better, use the alternative to OPENXML ( as in,
don't use OPENXML )

>I need to update add rows in a table from the UI. Rather that call my
> AddData stored proc 3 times to save 3 rows of data, I'd rather wrap
[quoted text clipped - 5 lines]
>
> Carl

Reply to this Message