Pronunciation / adjective
Bringing something good and unforseen.
    Skip Navigation Links > Code Repository
   

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.

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


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:
  1. DECLARE @Names xml;
  2. SET @Names = '<Names><Name>Smith</Name><Name>Jones</Name></Names>';
  3. SELECT *
  4. FROM Workers
  5. WHERE [LastName] IN
  6. (
  7. SELECT ParamValues.Name.value('.', 'NVARCHAR(255)')
  8. FROM @Names.nodes('/Names/Name') AS ParamValues(Name)
  9. )


Return
Microsoft Certified Professional   © 2017 Fortunate.  All rights reserved.
contact: justin@aboutfortunate.com