Passing lists to SQL Server 2005 with XML Parameters
Overview
SQL Server 2005's XML capabilities make it a easier to pass lists to SQL Server procedures.
Background
I recently needed to write a stored procedure which took a list of ID's as a parameter. That's one of those things that seems like it would be really simple, but isn't. You'd think you could just pass in a comma delimited string of id's: @ids = '3,5,7,8' and use something like 'SELECT * FROM Products WHERE ID IN (@ids)'. Nope, it doesn't work. I still remember my surprise when I ran into that six or seven years ago.
There are a huge variety of workarounds for this issue - see Erland's comprehensive list ranging form SQL Server 6.5 to 2000. I've used several of these, and while they worked I never liked them. Probably the best method is to just use a SPLIT table valued function which splits your string and returns a table. It's clean, but all of your procedures depend on the existence of that function.
It was also possible to use OPENXML in SQL Server 2000. The syntax was obviously put together by C++ programmers (you have to prepare a document and work with an integer handle, which feels a lot like a pointer), and there were some limitations to be aware of, but it pretty much worked.
This time around, I decided to try this with SQL Server 2005's XML capabilities and see if it was any easier. It is.
Getting started with SQL Server 2005's XML Syntax
XML variables in SQL Server 2005 make it easy to "shred" XML strings into relational data. The main new methods you'll need to use are value() and nodes() which allow us to select values from XML documents.
DECLARE @productIds xml
SET @productIds ='<Products><id>3</id><id>6</id><id>15</id></Products>'
SELECT
ParamValues.ID.value('.','VARCHAR(20)')
FROM @productIds.nodes('/Products/id') as ParamValues(ID)
Which gives us the following three rows:
3
6
15
Alright, just show me how to pass a list in a procedure parameter already!
Here's a proc which takes a single XML parameter. We first declare a table variable (@Products) and load the XML values into it. Once that's done, we can join against the @Products table as if it were any other table in the database.
CREATE PROCEDURE SelectByIdList(@productIds xml) AS
DECLARE @Products TABLE (ID int)
INSERT INTO @Products (ID) SELECT ParamValues.ID.value('.','VARCHAR(20)')
FROM @productIds.nodes('/Products/id') as ParamValues(ID)
SELECT * FROM
Products
INNER JOIN
@Products p
ON Products.ProductID = p.ID
Now we can call it as follows:
EXEC SelectByIdList @productIds='<Products><id>3</id><id>6</id><id>15</id></Products>'
Which gives us the following:
| ProductID |
ProductName |
SupplierID |
CategoryID |
QuantityPerUnit |
UnitPrice |
UnitsInStock |
UnitsOnOrder |
ReorderLevel |
Discontinued |
ID |
| 3 |
Aniseed Syrup |
1 |
2 |
12 - 550 ml bottles |
10 |
13 |
100 |
25 |
0 |
3 |
| 6 |
Grandma's Boysenberry Spread |
3 |
2 |
12 - 8 oz jars |
25 |
120 |
0 |
25 |
0 |
6 |
| 15 |
Genen Shouyu |
6 |
2 |
24 - 250 ml bottles |
15.5 |
39 |
0 |
5 |
0 |
15 |
In order to use this, you'll need to an XML string with your ID's. In our application, Steve was handling the application code, and I talked him into doing this via quick and dirty string concatenation. His method worked great:
public static string BuildXmlString(string xmlRootName, string[] values)
{
StringBuilder xmlString = new StringBuilder();
xmlString.AppendFormat("<{0}>", xmlRootName);
for (int i = 0; i < values.Length; i++)
{
xmlString.AppendFormat("<value>{0}</value>", values[i]);
}
xmlString.AppendFormat("</{0}>", xmlRootName);
return xmlString.ToString();
}
What's next?
This is a very simple use of XML in SQL Server. You can pass complex XML documents containing business objects to insert and update in your relational tables, for instance. If you're going to do that with a large amount of data, have a look at Ayende's clever use of SqlBulkCopy to handle that more efficiently.