A first exposure to XML and stored procedures in SQL Server 2005

by Tony 5. February 2009 18:08

I don't know what planet I've been living on for the past several years, but I JUST Discovered how useful XML can be with stored procedures and I wanted to write about it in hopes that I can help some other poor sap out there who's never even thought about it, or added it to his/her "To read about sometime" list.

Basically my problem is simple, a problem that many people have faced in the past. I wanted to pass one or more values to a stored procedure on SQL Server 2005. Coming from the VB 4 world, working through the ages up to C# I always remember having the use of an array available to me. A Simple concept I know, a series of items all related by some greater cause or purpose… but apparently not to SQL Server.

My First thought was a direct influence of my coding background, I'll just dynamically create a SQL Statement, and run it with an execute command:

DECLARE @SQL varchar(600)

SET @SQL =

'SELECT Error_Message

FROM dbo.Errors

WHERE Application_Id IN (' + @ListOfApplications + ')'

EXEC (@SQL)

Brilliant right? I pass in a comma separated list of applications, which than compiles into a string that is executed! I've done it a million times in the earlier days when I didn't know any better, and it worked than so why not now? Well turns out that one little word Compiles comes back to kick me in the ass. Every time my stored procedure runs this code needs to be compiled, and an execution path has to be figured out. That cannot be cheap resource wise. So I turned to Google, my best friend most days.

It wasn't long before I came across an MSDN article which highlighted the concept of XML for me, in a very obscure and difficult fashion. To use XML, they had me creating three extra stored procedures in the north wind database, and full applications just to test the call to the one that I wanted. So, after deciphering the typical Microsoft MSDN Confusion, I created this… my pride of the night:

ALTER PROCEDURE dbo.GetErrorsByAplications

(

@ApplicationIds nText

)

DECLARE @hDoc int

exec sp_xml_preparedocument @hDoc OUTPUT, @ApplicationIds

SELECT Error_Message

FROM Error

WHERE Application_Id IN (

SELECT Application_Id FROM OPENXML (@hdoc, '/Applications/Application', 1)

WITH (Application_Id NCHAR(5))

)

EXEC sp_xml_removedocument @hDoc

RETURN

Pure brilliance on the part of Microsoft if you ask me, and even as I sit here writing this I can only imagine the possibilities of expansion, what I could possibly do with XML in a stored procedure, you could say the possibilities where, well, endless. A Simple XML string is passed in:

Some magic happens and I get all of my results for Application 1 and 2, just like the first select statement, only significantly faster. I don't have time to figure out exactly how much faster right now, but it's definitely on my list of things to do! As far as I can tell, this little gem works like this:

Create an XML Document in SQL server, based on my nText String:

exec sp_xml_preparedocument @hDoc OUTPUT, @ApplicationIds
Use XPath to extract the elements from that document, specifically the Application_Id from the Application Tags:

SELECT Application_Id FROM OPENXML (@hdoc, '/Applications/Application', 1)

WITH (Application_Id NCHAR(5))
And finally kill the XML document and return the results:

EXEC sp_xml_removedocument @hDoc