So, we’ve had the ability to use XML within the SQL Server databases since SQL 2000 came out. What’s the big deal you ask? How can I use it to make my life easier you also ask?
When I first started looking into this whole XML thing I was thinking the same thing. How is this blob of data going to make my life easier? Well when used correctly it most certenly can. In SQL Server (up through SQL 2005 at least) we don’t have a way to pass more than one record into a stored procedure. If you have an order entry system and the custom wants to order 3 items you have to run three commands on the SQL Server, doing three inserts one at a time. If you instead passed those three items from the UI to the database as an XML document you could then process all three items in a single command thereby improving performance.
declare @XML XMLSET @XML = '<ROOT><Customer ID="12">
<Order ID="7498">
<Item ID="415" Qty="1" />
<Item ID="87547" Qty="2" />
<Item ID="4125" Qty="1" />
</Order>
</Customer>
</ROOT>'
DECLARE @hDoc INT
exec sp_xml_preparedocument @hDoc OUTPUT, @XML
SELECT *
FROM OPENXML (@hDoc, '//Customer/Order/Item')
WITH (CustomerId INT '../../@ID',
OrderId INT '../@ID',
ItemId INT '@ID',
Qty INT '@Qty')
exec sp_xml_removedocument @hDoc
As you can see by the 0utput we have a table which we can process in bulk. Instead of running a sample piece of code if we were to build this into a stored procedure we could do something like this.
Here is our LineItems table.
CREATE TABLE LineItems
(OrderId INT,
ItemId INT,
Qty INT,
ItemPrice NUMERIC(6,2))
And here is our procedure which adds the XML data to the LineItems table. In a production system business logic would need to be added to ensure stock on hand, and to return estimated ship dates to the client.
CREATE PROCEDURE usp_AddLineItems
@Items XML
AS
DECLARE @hDoc INT
exec sp_xml_preparedocument @hDoc OUTPUT, @Items
INSERT INTO LineItems
SELECT OrderId, ItemId, Qty, ItemPrice
FROM OPENXML (@hDoc, '//Customer/Order/Item')
WITH (CustomerId INT '../../@ID',
OrderId INT '../@ID',
ItemId INT '@ID',
Qty INT '@Qty',
ItemPrice NUMERIC(6,2)
'@ItemPrice') exec sp_xml_removedocument @hDocGO
Since we don’t want to create a web based order system for this little demo here we can run the procedure and verify the output.
declare @XML XMLSET @XML = '<ROOT><Customer ID="12">
<Order ID="7498">
<Item ID="415" Qty="1" ItemPrice="12.95"/>
<Item ID="87547" Qty="2" ItemPrice="16.25"/>
<Item ID="4125" Qty="1" ItemPrice="8.25"/>
</Order>
</Customer>
</ROOT>'
EXEC usp_AddLineItems @XMLGO
SELECT *
FROM LineItemsGO
As we can see this is much more efficient than having to run three insert statements as they would require at least three rights to the disk, plus how ever many reads are needed to do the business logic. By using the XML we can do it all with a single write to the disk, and a single set of reads for the business logic. In this example we have reduced our disk activity by 33%. If we were processing 10 line items we would have reduced our disk activity 90%. And since disk performance is usually the bottleneck of any database reducing the disk IO any way we can is a good thing.
Denny
