Passing Arrays to SQL 2005 As XML

Return

Passing Arrays to SQL 2005 As XML

One method of passing an array to a SQL Stored Procedure via a single parameter is to turn the array into XML and send the XML. The following code shows how to then consume that parameter and use it in a "WHERE IN" SQL SELECT statement. The example assumes a list of worker's ids to retrieve records for is being sent in.

DECLARE @IDList xml;
SET @IDList = '<IDsToSearch><ID>1</ID><ID>5</ID></IDsToSearch>';
SELECT *
FROM Workers
WHERE [WorkerId] IN
(
SELECT ParamValues.ID.value('.', 'INT')
FROM @IDList.nodes('/IdsToSearch/ID') AS ParamValues(ID)
)

Keep in mind that any data type may be passed in and retrieved. For example if names were being passed in then the SELECT statement would look like this:

DECLARE @Names xml;
SET @Names = '<Names><Name>Smith</Name><Name>Jones</Name></Names>';
SELECT *
FROM Workers
WHERE [LastName] IN
(
SELECT ParamValues.Name.value('.', 'NVARCHAR(255)')
FROM @Names.nodes('/Names/Name') AS ParamValues(Name)
)

Return