Passing a list of values into a stored procedure with XML

Imagine you have a list of unrelated items in your .NET application, and you need SQL Server to do something for each one. For example:

  • A customer has a shopping cart containing a list of 10 product IDs. The shopping cart is stored in ASP.NET session memory on the web server. How can you retrieve details about these ten products without knocking together some horrific WHERE clause, or executing 10 separate SELECT statements?
  • An administration section of an application allows a user to mass-edit a list of items, and save them all with one click. But the usp_updateItem stored procedure can only save one item at a time.

To minimise the number of round-trips to the database, you need to pass in multiple items at once to the same stored procedure. This is where an XML type parameter can help.

Here’s a fragment of XML containing the list of employee names and IDs I want to pass to my stored procedure:

<employees>
      <employee employeeId="401312" name="John Smith" />
      <employee employeeId="345334" name="John Doe" />
      <employee employeeId="997889" name="Jane Doe" />
</employees>

I’ll populate a table variable (so I can JOIN on it later) with an XPath query using the XML data type’s nodes() method. The technical term for this is shredding, which is pretty rad.

CREATE PROCEDURE FooBar(@employees XML)
AS
BEGIN
      -- Create a table variable to store my items.
      DECLARE @employee TABLE(EmployeeID INT, Name VARCHAR(20))

      -- Shred data carried in the XML and populate the table variable with it.
      INSERT INTO @employee
      SELECT
            e.value('@employeeId', 'INT'),
            e.value('@name', 'VARCHAR(20)')
      FROM
            @employees.nodes('//employee') Employee(e)

      -- Select from table variable as usual.
      SELECT * FROM @employee e
END

Easy, huh? You can easily pass in a set of values with one XML parameter and a couple of lines of T-SQL. Note that you can of course simply shred the XML directly, as part of a bigger query – the temporary table variable is completely optional.

Passing in multiple columns isn’t a problem either. In fact, if you want to go really crazy with this stuff, you could even handle n-dimensional data structures by using nested XML elements.

This is my last article on T-SQL, by the way. I promise.