Prev: SQL Server 2000 SP4 Becomes clogged!!!
Next: Upsert Proc using IF logic
From: Carl Ganz on 20 Jul 2008 18:11
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
From: Plamen Ratchev on 20 Jul 2008 20:39
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

From: Erland Sommarskog on 21 Jul 2008 05:09
Carl Ganz (seton.software(a)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(a)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
From: sloan on 21 Jul 2008 11:32

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" <seton.software(a)verizon.net> wrote in message
news:4df334b2-ed88-4026-8a78-431a8ff1eecd(a)y38g2000hsy.googlegroups.com...
>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


From: Carl Ganz on 22 Jul 2008 11:59
On Jul 21, 5:09 am, Erland Sommarskog <esq...(a)sommarskog.se> wrote:
> CarlGanz (seton.softw...(a)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...(a)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
 |  Next  |  Last
Pages: 1 2
Prev: SQL Server 2000 SP4 Becomes clogged!!!
Next: Upsert Proc using IF logic