Pass multiple strings to stored procedure IN statement using XML

(Revised 3.18.08)

Let’s say you have a listbox with multiple items selected, like this:listboxAnd let’s say that you want to take the selected items and send them to a stored procedure to run in an IN statement in the WHERE clause, like this: SELECT * FROM tblProjects WHERE ProjectCity IN ('Lewiston', 'Portland', 'Spokane')

Turns out it’s not a simple operation, because you can’t send an array to a stored procedure, and if you send it as a comma-separated string, SQL Server just thinks it’s one big string.If you go the comma-separated string route, you can break down the string and loop through its parts using T-SQL, but it’s clunky. Another way is to send the values as XML and then query the XML in the stored procedure.

First, retrieve the values from the listbox and put them in an xml string:

'initialize a new stringbuilder object
Dim sb As New StringBuilder

'add the root element of the xml string to the stringbuilder
sb.Append("<root>")

'loop through the listbox items
For Each li As ListItem In Me.lstCities.Items

'if the item is selected, add it to an xml element string, and add
'the string to the stringbuilder
If li.Selected Then
sb.Append(String.Format("<item value=""{0}"" />", li.Text))
End If
Next

'add the root element closing tag to the stringbuilder
sb.Append("</root>")

Calling sb.ToString will produce the following value:

<root>
<item value="Lewiston" />
<item value="Portland" />
<item value="Spokane" />
</root>

At this point you’d load the sb.ToString value in a parameter and send it to the stored procedure:

'use stringbuilder value to create a sqlParameter object
Dim parameter As New Data.SqlClient.SqlParameter
With parameter
.ParameterName = "@XMLDoc"
.Value = sb.ToString
.DbType = Data.DbType.String
End With

'access database .....

OK, now we move to the SQL Server side, to figure out exactly what to do with this xml string once it arrives. In SQL Server 2000, you’ll need to use the OPENXML function to use XML as a datasource. In SQL Server 2005, because of its support for the xml data type, you can query the xml directly.

Let’s do a SQL Server 2000 example first. To use OPENXML, you’ve got to prepare the XML first by calling sp_xml_preparedocument.This system stored procedure prepares an internal representation of the xml document, and returns a handle that can be used to access the internal representation of the document.

Once you’ve got the document handle (which is an integer), you can use OPENXML to query the xml data:

declare @XMLDoc varchar(1000)
set @XMLDoc = '
<root>
<item value="Lewiston" />
<item value="Portland" />
<item value="Spokane" />
</root>
'

/* set variable for document handle*/
DECLARE @XMLDocHandle INT

/* call sp_xml_preparedocument. Send the document handle variable as an output parameter, and the xml document you want prepared*/
EXEC sp_xml_preparedocument @XMLDocHandle OUTPUT, @XMLDoc

/* use OPENXML to query the in-memory xml doc representation */
SELECT value
FROM OPENXML(@XMLDocHandle,'/root/item',2)
WITH (value VARCHAR(50) '@value')

/* call sp_xml_removedocument to remove the in-memory doc representation when you are done with it*/
EXEC sp_xml_removedocument @XMLDocHandle

OPENXML accepts three arguments – the handle of the in-memory xml doc to be queried, the XPath Query used to identify the nodes to be processed, and a flag that indicated the mappings between the xml doc and the in-memory rowset. The WITH clause identifies the XML data to be retrieved, and the SQL data type it should be returned as.

And here’s what you’d do in SQL Server 2005:

/*Note the xml data type, rather than varchar(n)*/
declare @XMLDoc xml
set @XMLDoc = '
<root>
<item value="Lewiston" />
<item value="Portland" />
<item value="Spokane" />
</root>'
SELECT x.Item.value('@value', 'varchar(50)')
FROM @XMLDoc.nodes('/root/item') AS x(Item)

The xml data type has five methods, two of which, nodes and value, are used here: nodes to propogate the ‘item’ elements in to a rowset, and value to retrieve a specific value from the rowset. The nodes method accepts one parameter, an XPath Query to retrieve the desired xml data. The value method accepts two parameters, an XPath Query to retrieve the desired xml data, and a SQL type that states what type the return value should be. Note that at the end of the FROM statement, there is an alias for both tablename (x) and column name (item), and that these aliases are used in the SELECT statement.

Both of the above T-SQL examples produce the following results:

value
----------
Lewiston
Portland
Spokane

(Note: if you copy and paste these examples in to SQL Server to run, you may need to re-type all the apostrophe’s and quotation marks in the T-SQL statement first or it will generate errors)

OK, that looks like something you could use in an IN statement. Now, we just need to build our stored procedure, querying the xml in a subquery in the IN statement. Here’s how it would look in SQL Server 2000:

CREATE PROCEDURE [dbo].[uspUseXML]

@XMLDoc VARCHAR(2000)

AS

DECLARE @XMLDocHandle INT

EXEC sp_xml_preparedocument @XMLDocHandle OUTPUT, @XMLDoc

SELECT * FROM tblProjects WHERE ProjectCity IN(
SELECT value
FROM OPENXML(@XMLDocHandle,'/root/item',2)
WITH (value VARCHAR(50) '@value'))

EXEC sp_xml_removedocument @XMLDocHandle

And here’s how we’d do it in SQL Server 2005 —
CREATE PROCEDURE [dbo].[uspUseXML]

@XMLDoc xml

AS

SELECT * FROM tblProjects WHERE ProjectCity IN(
SELECT x.Item.value('@value', 'varchar(50)')
FROM @XMLDoc.nodes('/root/item') AS x(Item))

Another way to do this would be to join the projects table and the xml using an INNER JOIN. Here’s the SQL Server 2000 example:

CREATE PROCEDURE uspUseXMLInJoin

@XMLDoc VARCHAR(2000)

AS

DECLARE @XMLDocHandle INT

EXEC sp_xml_preparedocumnet @XMLDocHandle OUTPUT, @XMLDoc

SELECT p.*
FROM tblProjects p
INNER JOIN
OPENXML(@XMLHandle, '/root/item',2)
WITH (city VARCHAR(50) '@value') as x
ON p.ProjectCity = x.city

And in SQL Server 2005:

CREATE PROCEDURE uspUseXMLInJoin

@XMLDoc xml

AS

SELECT p.*
FROM tblProjects p
INNER JOIN @XMLDoc.nodes('/root/item') AS x(City)
ON p.ProjectCity = x.City.value('@value', 'varchar(50)')

It’s beyond what I want to get in to here, but XML has many other nifty uses in SQL Server, especially in being able to update several rows of data without having to make several database calls. The MSDN site has lots of info, and there is also a very good article (free registration required) at SQLServerCentral.com that gives lots of examples and variations on its use. In the course of researching this, I also found an extremely excellent online article describing in great detail other ways of dealing with Arrays and Lists in SQL Server.

Advertisements