There is a probability of encountering the need to pass an array of data from a front end .NET/FLEX application or WCF/SOA Web Service at some point during the development process for an application. For example, perhaps you are developing an online order entry system which will require that multiple items from your application get written to the order header and order details tables.
There are a few ways to do this through T-SQL/stored procedures, but the two primary approaches are to: (l) create user functions that parse a delimiter to separate the elements in a string; (2) use the XML features available in Microsoft SQL Server.
If you are using SQL Server 6.5/7.0, the XML features won’t be available. so you’ll need to go the route of user defined functions. This post does not address these methods, but here is my favorite link on the topic.
If you are using SQL Server 2000, the tools available for use are the legacy OPENXML rowset provider and the system stored procedures: sp_xml_preparedocument, sp_xml_removedocument . In SQL Server 2005 and 2008, these tools still exist, however the preferred XML data type methods for these versions of SQL Sever are: nodes(), value(), and query(). Here are two excellent posts for anyone working with OPENXML (or data arrays) in SQL Server 2000:
http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
http://www.mssqltips.com/tip.asp?tip=1609
In this practical Order Header/Order Detail example, I’ll be using the xml data type methods: value() and nodes(). To view the relational design pattern used for this example, Fernando Loranzo’s Introduction to Relational Database Design article illustrates the OrderHeader and OrderDetails tables with Order and Order_Items tables.
T-SQL/Stored Procedure
This procedure accepts two basic parameters, the account id (@AccountID) of the customer and the product items/product quantities bound passed as an xml document (@OrderList). For the OrderHeader table, the identity feature of SQL Server is invoked on an insert creating a unique OrderID. The OrderID is captured in the declared variable (@OrderID) for use when inserting the product/quantity data into the OrderDetails table. To do the insert, the sub-select uses the OrderList xml document (as ‘D’), drilling through the nodes (/Order/Item/Prod) to parse through the document, obtaining the values (e.g. D.element.value).
create proc [dbo].[InsertOrder]
(
@OrderList xml,
@AccountID int
)
as
set nocount on
declare @OrderID int
declare @Today datetime
set @Today = getDate()
insert into [dbo].[OrderHeader]
(CustAcct,OrderDate)
values
(@AccountID,@Today)
set @OrderID = SCOPE_IDENTITY()
insert into [dbo].[OrderDetail]
(OrderID,ProdId,ProdQty)
select
@OrderID,
D.element.value(‘@ID’, ‘nvarchar(255)’) as ID,
D.element.value(‘@Qty’,'nvarchar(255)’) as Qty
from @OrderList.nodes(‘/Order/Item/Prod) as D(element)
XML for the OrderList
In order for the stored procedure InsertOrder to work, the xml which is passed (@OrderList) is in the following format:
<Order>
<Item>
<Prod ID=”60000″ Qty=”200″></Prod>
</Item>
<Item>
<Prod ID=”60180″ Qty=”1000″></Prod>
</Item>
</Order>
To test this stored procedure:
Open a query window and type the execute statement, wrapping the OrderList xml in single quotes and the customer number/account id in single quotes (optional as AccountID is defined as an integer).
exec InsertOrder
‘<Order>
<Item>
<Prod ID=”60000″ Qty=”200″></Prod>
</Item>
<Item>
<Prod ID=”60180″ Qty=”1000″></Prod>
</Item>
</Order>’, ‘222333‘
Copyright © Paula DiTallo 2009 All Rights Reserved
