SQL Server 2000 is an old database platform now, but it is still fairly widely used. That’s why it’s good to know how to pass a list of parameters to a stored procedure on the SQL Server 2000 database platform. Likewise, the code is forwards compatible and will work with more modern versions of SQL Server such as 2008.
One of the safest ways to pass a list of values is using XML. Since SQL Server 2000 did not yet have the XML datatype, you need to pass your XML as a VARCHAR value. Likewise, your XML cannot be too large since SQL Server 2000 had an 8000 character limit on the VARCHAR datatype.
When you pass your XML as a VARCHAR, you will need to use sp_xml_preparedocument to make it query-able. After you are done working with your XML, don’t forget to use sp_xml_removedocument or you may encounter memory leaks.
When your XML is ready to be queried, you can use OPENXML to query your list like you would a regular table.
Here is an example SQL snippet that takes a VARCHAR parameter, populates it with simple XML of Name and Email addresses of two ficticous people, and then queries the XML like a standard SQL table:
DECLARE @SupplementalUsers VARCHAR(8000) SET @SupplementalUsers = '<ItemList><data><name>Bob Test</name><email>firstname.lastname@example.org</email></data><data><name>Jim Test</name><email>email@example.com</email></data></ItemList>' DECLARE @SupplementalAddresses INT EXEC sp_xml_preparedocument @SupplementalAddresses OUTPUT, @SupplementalUsers SELECT name,email FROM OPENXML(@SupplementalAddresses,'/ItemList/data', 2) WITH (name VARCHAR(200), email VARCHAR(200)) EXEC sp_xml_removedocument @SupplementalAddresses
In this example, OPENXML takes 2 as a parameter since the XML is in Element-Centric mapping (ie: the data is contained within the name and email nodes). The default mapping is 0 (Attribute-Centric mapping)
You can query the data from the OPENXML query using joins to regular database tables, which makes this a very useful technique.
Here is a link to a Microsoft Technet that gives an explanation of OPENXML