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.
Have a look here if you are looking for a working example of passing XML to a stored procedure using SQL Server 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>email@example.com</email></data><data><name>Jim Test</name><email>firstname.lastname@example.org</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