Passing Arrays in SQL Parameters using XML Data Type in SQL Server 2005

• Download demo project - 7.0 KB

Introduction

Passing arrays of values as SQL parameters has always been troublesome in T-SQL. In SQL Server 2005, the XML data type can help simulate arrays.

Background

T-SQL doesn't have the notion of arrays; it only has simple types (e.g. INTEGER, VARCHAR). A typical scenario with a web or application server is to call a stored procedure with multiple arguments. For instance, you might do an information treatment and determine that an array of customer IDs should be marked with a given status. The intuitive way of implementing that scenario would be to pass the customer ID list to the stored procedure:

private void PushStatus(int[] customerIDs, CustomerStatus status)
{
    CallSproc(customerIDs, status);
}

Unfortunately, you won't be able to write a stored procedure accepting an array of INTEGERs as an input parameter. A possible way of implementing the scenario is to call the stored procedure multiple times, once for every ID:

private void PushStatus(int[] customerIDs, CustomerStatus status)
{
    foreach(int id in customerIDs)
    {
        CallSproc(id, status);
    }
}

This is a popular method, but it doesn't scale very well. If the array of IDs you want to pass is large (for instance, more than 10 elements), the latency of the stored procedure calls will start to kill the performance of the overall operation. In order for performance to remain acceptable, we would need to pass the whole array to the database server in one call, or at least in much fewer calls than the size of the array. A popular solution prior to SQL Server 2005 was to concatenate the array into a string and pass that string to a stored procedure. There were several problems with that solution:

• Splitting the string in T-SQL was possible, but was a cumbersome procedure and a slow one. This is due to the fact that T-SQL isn't a language optimized to manipulate strings.
• As with all serialization process, you had to choose a separator character (e.g. the pipe) and condemn this character from the input or escape it. That introduced complexity on both end.
• The input was a string (a varchar or text) and therefore, semantically, this technique was confusing for somebody reading the stored procedure.

Using XML

One of the big highlights of SQL Server 2005 is the introduction of the XML data type. XML is great for transporting all sorts of structural information. Using XML, we could implement the scenario from the previous section. We would simply convert an array (e.g. {42, 73, 2007}) into an XML document:

<list>
    <item>42</item>
    <item>73</item>
    <item>2007</item>
</list>

This approach would have many advantages:

• Extracting the information from the XML document in T-SQL is straightforward using the x-query capability of T-SQL.
• There are special characters in the serialization process (e.g. <, >), but the escaping mechanism of XML is well known and is taken care of by T-SQL.
• The semantics of the stored procedure are somewhat clearer, since XML always represents a package of information.

Using the Code

The mini-library proposed in this article consists of a static helper class in C# and a T-SQL function. The static class has one method:

public static SqlXml GetXml(IEnumerable list)
{
    //We don't use 'using' or dispose or close the stream, 
    //since it leaves in the return variable
    MemoryStream stream = new MemoryStream();

    using (XmlWriter writer = XmlWriter.Create(stream))
    {
        writer.WriteStartElement("list");

        foreach (object obj in list)
        {
            writer.WriteElementString("item", obj.ToString());
        }

        writer.WriteEndElement();
        stream.Position = 0;

        return new SqlXml(stream);
    }
}

It takes a list of objects (any type of object; it simply relies on the ToString implementation) and returns an XML variable ready to be fed to an SqlParameter. On SQL Server, the function is an inline table function:

CREATE FUNCTION [lm].[SplitList]
(
    @list AS XML
)
RETURNS TABLE
AS
RETURN
(
    SELECT tempTable.item.value('.', 'VARCHAR(MAX)') AS Item
    FROM @list.nodes('list/item') tempTable(item)
);

It takes an XML parameter and returns a table with one column where each row is the content of an XML node.

Conclusion

The technique shown in this article shows yet another interesting benefit of having XML as a first class citizen in SQL Server 2005. Passing an array of objects to a stored procedure improves performance for batch operations. This can improve both the performance and scalability of an application.