Home > IT Blogs > SQL Server with Mr. Denny >

SQL Server with Mr. Denny:

XML

Jan 7 2008   8:00AM GMT

XML in the database. What’s the big deal any way?



Posted by: mrdenny
XML, Data Types, T/SQL

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

AddThis Social Bookmark Button     0 Comments     RSS Feed     Email a friend

Jan 3 2008   8:00AM GMT

Using a foreach loop to process an XML document.



Posted by: mrdenny
SQL, DataManagement, XML, SQL Server 2005, SQL Server 2008, SSIS

Receintly I was working on a project where I needed to use a foreach loop with an SSIS project, but couldn’t for the life of me get it to properly process the XML document which I was giving it.  Well with some major work and digging I was able to get it working correctly, but it took me for ever to get all the little setting correct so I figured that I’d throw the info up here for anyone else who is looking for it.

Some background on what the process is that I’m working on.  Basically I’ve got a table with catagorized data in it.  I need to export all the data from the table info one file per catagory (don’t ask, I didn’t design it, I’ve just got to automate it; and it actually makes sence in the grant schem of things).  Well I figured that the easiest way to do this was to use a foreach loop and give it an XML document with the list of catagories to process.  (This was better than looping through and getting the next value from the database.)

 So needless to say, I get started on my little process.

The query which I’m using within an Execute SQL Task is below.  The Execute SQL Task puts the XML data into an SSIS variable called v_CategoryList with a data type of string.

SELECT WebsiteBlockCategoryId as id
FROM dbo.WebsiteBlockCategory cat with (nolock)
where WebsiteBlockCategoryId  <> 0
for XML AUTO

The XML document looks like this. (SSIS seams to be wrapping it within <ROOT></ROOT> tags for me which is why I’m not doing it my self.)  My XML document is actually must longer than this, but you get the idea.  It’s a very basic XML document.

<ROOT>
 <cat id=”18″ />
 <cat id=”19″ />
 <cat id=”20″ />
 <cat id=”21″ />
 <cat id=”22″ />
 <cat id=”23″ />
 <cat id=”24″ />
 <cat id=”25″ />
 <cat id=”26″ />
 <cat id=”27″ />
 <cat id=”28″ />
</ROOT>

As you can see on the screenshot of the forloop properties I’ve set the source to be my variable and the EnumerationType to Element Collection.  Since I know where the data is within the XML document I use DirectInput for both the outer and Inner XPath strings.  For the Outer XPath string I’m using “//cat”.  Because I’m not putting in the ROOT level name it doesn’t matter what gets put in there as long as there is a parent level.    For the Inner XPath string I’ve got the Element name with an @ sign in front of it “@id”. 

Collection Screen

Over on the Variables page of the UI I’ve got my v_CategoryId variable mapped to Index 0 of my document.  If you need to output more than one element from your XML document set your Inner XPath to “*”.  This “should” allow you to bring back all the elements and refer to them by index number starting with 0.  I havn’t actually tried this, as I’ve always only needed a single element hense the “should”.

Variable Mappings Screen

And to think, that little thing took me a couple of days to get working right.  I can only imagine trying to do this in SQL 2000 and DTS.

Denny

AddThis Social Bookmark Button     0 Comments     RSS Feed     Email a friend