Sending a list into an SQL stored procedure using XML
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