Feeding XML to a Stored Procedure
This article may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. To maintain the flow of the article, we've left these URLs in the text, but disabled the links.
.gif)
Tom Moreau
Let's face it—XML (eXtensible Markup Language) is coming your way soon. Now, while Tom Moreau's not an XML kind of guy—he's of the T-SQL persuasion—he does see a use for it when you want to add some hierarchical data through a single stored procedure. Read on.
One thing that's bothered me for some time is the inability to add, say, an order and all of its details to a SQL Server database via a single stored procedure call. (Back in the pre-SQL 2000 days, I received more than one plea to do just that, but I was unable to come up with a solution.) Sure, if your order always has a fixed number of details, you could code it up. However, life isn't always that tidy. Typically, an order can have any number of detail rows, and this is where things get nasty. The way I've handled this in real life is to have the calling code begin a transaction and then issue one stored proc call for the order followed by a bunch of calls for the details—and, of course, the commit. But I've never been happy with the network traffic and the fact that a transaction is initiated at the client end.
XML support is undoubtedly the most important new feature in SQL Server 2000. Basically, XML is a standardized way of sending data over the Internet. The XML documents are tagged text and most often have a hierarchical structure to them. For example, you can have customers and, within each customer, orders, and, within each order, details.
The XML document exists as a single unit. On the receiving end, your job is to parse the XML document, extract the data, and load it into your SQL Server tables. In this month's code, we pass the XML document as a parameter (ntext for maximum file size) to our stored procedure, sp_XMLOrder. We need to prepare the document by calling sp_xml_preparedocument before feeding into our own routine. It (sp_xml_preparedocument) returns a handle through an output parameter, and it's this handle that we use in calls to the OPENXML() rowset provider. We can make as many calls to OPENXML() as we want, changing the Xpath pattern each time to extract the particular rowsets we need. Finally, we have to free up memory by calling sp_xml_releasedocument. This example uses Northwind:
use Northwind
go
create proc sp_XMLOrder
(@OrderDoc ntext)
as
set nocount on
declare
@hDoc int
, @Trancount int
declare
@map table
( InputOrderID int not null
identity
primary key
, OrderID int not null
unique)
declare
@check table
( InputOrderID int not null
identity
primary key
, OrderID int not null
unique)
-- pick up handle to XML document
exec sp_xml_preparedocument
@hDoc output
, @OrderDoc
-- populate check table
insert @check
(OrderID)
select
OrderID
from
openxml (@hDoc, '/ROOT/Order', 1)
with
( OrderID int
, CustomerID nchar(5)
, EmployeeID int
, OrderDate datetime
, RequiredDate datetime
, ShipVia int
, ShipAddress nvarchar (60)
, ShipCity nvarchar (15)
, ShipRegion nvarchar (15)
, ShipPostalCode nvarchar (10)
, ShipCountry nvarchar (15) ) x
order by
OrderID
-- check if XML orders begin with 1 and are contiguous
if exists
( select * from @check
where InputOrderID <> OrderID)
begin
raiserror ('Out of synch OrderID.', 16, 1)
exec sp_xml_removedocument
@hDoc
return
end
-- start transaction
set
@Trancount = @@TRANCOUNT
if @Trancount = 0
begin tran sp_XMLOrder
else
save tran sp_XMLOrder
-- add orders
insert Orders with (repeatableread)
( CustomerID
, EmployeeID, OrderDate,RequiredDate, ShipVia
, ShipAddress, ShipCity, ShipRegion, ShipPostalCode
, ShipCountry)
select
CustomerID, EmployeeID, OrderDate, RequiredDate
, ShipVia, ShipAddress, ShipCity, ShipRegion,
, ShipPostalCode, ShipCountry
from
openxml (@hDoc, '/ROOT/Order', 1)
with
( OrderID int
, CustomerID nchar(5)
, EmployeeID int
, OrderDate datetime
, RequiredDate datetime
, ShipVia int
, ShipAddress nvarchar (60)
, ShipCity nvarchar (15)
, ShipRegion nvarchar (15)
, ShipPostalCode nvarchar (10)
, ShipCountry nvarchar (15) )
order by
OrderID
-- populate mapping table of
-- inserted orders to XML orders
insert @map
(OrderID)
select
o.OrderID
from
Orders o
where not exists
( select * from [Order Details] od
where od.OrderID = o.OrderID
order by o.OrderID
-- add order details from XML document
insert [Order Details]
( OrderID, ProductID, UnitPrice
, Quantity, Discount)
select
t.OrderID, x.ProductID, x.UnitPrice
, x.Quantity, x.Discount
from
openxml (@hDoc, '/ROOT/Order/OrderDetails', 1)
with
( OrderID int '../@OrderID'
, ProductID int
, UnitPrice money
, Quantity smallint
, Discount real) x
join @map t on t.InputOrderID = x.OrderID
if @Trancount = 0
commit tran
exec sp_xml_removedocument
@hDoc
go
exec sp_XMLOrder
'
<ROOT>
<Order OrderID="1" CustomerID="VINET" .../>
<OrderDetails ProductID="6" UnitPrice= .../>
<OrderDetails ProductID="7" UnitPrice= .../>
<OrderDetails ProductID="8" UnitPrice= .../>
</Order>
<Order OrderID="2" CustomerID="ALFKI" .../>
<OrderDetails ProductID="2" UnitPrice= .../>
<OrderDetails ProductID="4" UnitPrice= .../>
<OrderDetails ProductID="5" UnitPrice= .../>
</Order>
<Order OrderID="3" CustomerID="BOTTM" .../>
<OrderDetails ProductID="1" UnitPrice= .../>
<OrderDetails ProductID="3" UnitPrice= .../>
<OrderDetails ProductID="9" UnitPrice= .../>
</Order>
</ROOT>
The stored proc receives an XML document that has one or more orders and their details. (I've stripped out most error-handling due to space limitations, and the XML document has been trimmed for the same reason.) It prepares the document and receives a handle, which it then uses to extract the order information through a call to OPENXML. You specify that you want the Orders part by giving it an Xpath of /ROOT/Orders/. Here's the rub—the calling program doesn't know the real OrderID ahead of time since it's an IDENTITY column in the Orders table. Still, you need a primary key—or at least something that looks like a primary key—on the orders part of the XML document. To get around this, I added a dummy OrderID, starting at 1 and incrementing by 1. (The increment is the same as that in the Orders table.) To enforce this, I created the @check table, which itself has a column—OrderID—that's an IDENTITY column. By loading the @check table with the dummy keys, sorted in ascending order, I can check to see whether the XML document meets the criteria for the keys.
Once the XML document has passed the "key" test, I begin a transaction and insert the orders into the Orders table. I still need to correlate the inserted rows of the Orders table with the orders in the XML document. I do this by loading the @map table, which has an IDENTITY column—InputOrderID. Since this table is loaded with rows sorted by OrderID, the result is a mapping of the OrderID of the XML document to the OrderID of the Orders table.
Once the @map table has been populated, you can join the order details part of the XML document onto it and insert the order details into the Order Details table. This time, the Xpath is /ROOT/Orders/OrderDetails. The OrderID from the Orders table is required, since it forms part of the primary key of the Order Details table. This is obtained through the join onto the @map table. Even though an OrderID doesn't exist in the OrderDetail part of the XML document, it can be inherited by specifying ../@OrderID in the table layout in the call to OPENXML. This is the OrderID of the dummy primary key for the order—the parent of the order detail.
Once you've added the order details, the transaction is committed, and you're done. Now you have a way of feeding multiple orders and their details to SQL Server in a single stored proc call. The complete code is available in the accompanying Download file. And, to stay on top of developments in the XML world, consider subscribing to our sister publication—XML Developer—at www.xmldevelopernewsletter.com.