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

Carl Ganz (seton.software@verizon.net) writes:
> 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.


--
Erland Sommarskog, SQL Server MVP, esquel.DeleteThis@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
 >> Stay informed about: Iterating XML in SQL Server 2005 (or 2008) stored procedure 

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 )





"Carl Ganz" wrote in message

>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
 >> Stay informed about: Iterating XML in SQL Server 2005 (or 2008) stored procedure 

On Jul 21, 5:09 am, Erland Sommarskog wrote:
> CarlGanz (seton.softw...@verizon.net) writes:
> > 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.
>
> --
> Erland Sommarskog, SQL Server MVP, esq... RemoveThis @sommarskog.se
>
> 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
 >> Stay informed about: Iterating XML in SQL Server 2005 (or 2008) stored procedure 

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
 >> Stay informed about: Iterating XML in SQL Server 2005 (or 2008) stored procedure 

Carl Ganz (seton.software@verizon.net) writes:
> 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>

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.RemoveThis@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
 >> Stay informed about: Iterating XML in SQL Server 2005 (or 2008) stored procedure 

On Jul 22, 6:10 pm, Erland Sommarskog wrote:
> CarlGanz (seton.softw...@verizon.net) writes:
> > 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>
>
> 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, esq... RemoveThis @sommarskog.se
>
> 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
 >> Stay informed about: Iterating XML in SQL Server 2005 (or 2008) stored procedure