I wanted to pass a list of user IDs into a stored procedure. What I really wanted was to be able to pass an array, but you're not able to do that because it would be too easy. It took me quite a while to figure this out, but now I know how, it'll be easy next time.

There's a nice set of functions that allow you to get values from an XML string and treat the set of values like a table. Just what I wanted from arrays really.

Here is the XML string that I used:

<users>
  <user id="24" />
  <user id="27" />
</users>

And here is the relevant part of the stored procedure I made:

CREATE PROCEDURE pXmlTest
  -- Parameters
  @xmlStr varchar(255)
  AS
  BEGIN
    SET NOCOUNT ON;

    DECLARE @xmlHandle int;

    -- Prepare XML document and get a handle for it
    exec sp_xml_preparedocument @xmlHandle OUTPUT, @xmlStr;

    -- You can now use the OPENXML function which returns a rowset that behaves like a table
    -- WITH chooses the field names you want to use from the XML and declares the type they will be.
    SELECT * FROM tUsers WHERE id IN
    ( SELECT id FROM OPENXML(@xmlHandle, '/users/user') WITH (id varchar(50)) );
  END