SQL Server 2000: Passing a List with XML

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>bob@123test.com</email></data><data><name>Jim Test</name><email>jim@123test.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