Click here to Skip to main content
Click here to Skip to main content

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

By , 12 Oct 2007
Rate this:
Please Sign up or sign in to vote.

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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

About the Author

Vincent-Philippe Lauzon
Architect CGI
Canada Canada
Vincent-Philippe is a Senior Solution Architect working in Montreal (Quebec, Canada).
 
His main interests are Windows Azure, .NET Enterprise suite (e.g. SharePoint 2013, Biztalk Server 2010) & the new .NET 4.5 platforms.

Comments and Discussions

 
GeneralNice solution Pinmemberkoche01216-Jul-09 5:22 
GeneralRe: Nice solution PinmemberVincent-Philippe Lauzon16-Jul-09 5:25 
Questionhow to insert data using this xml Pinmemberranger301014-Jan-09 2:35 
GeneralYou are a better man than me. PinmemberS432**%$20-Jun-08 9:58 
GeneralXML in stored proc [modified] Pinmembersujit.patil2-Mar-08 20:24 
GeneralRe: XML in stored proc PinmemberVincent-Philippe Lauzon3-Mar-08 7:36 
QuestionProblem while Hierarchy data PinmemberSachin Pisal6-Nov-07 11:32 
AnswerRe: Problem while Hierarchy data PinmemberVincent-Philippe Lauzon7-Nov-07 2:45 
GeneralRe: Problem while Hierarchy data PinmemberSachin Pisal7-Nov-07 6:28 
GeneralXML schema for validating the XML input Pinmemberbertkid12-Oct-07 9:19 
GeneralRe: XML schema for validating the XML input PinmemberVincent-Philippe Lauzon12-Oct-07 9:42 
GeneralRe: XML schema for validating the XML input Pinmemberbertkid17-Oct-07 8:32 
QuestionWhy? PinmemberGogza12-Oct-07 5:16 
AnswerRe: Why? PinmemberVincent-Philippe Lauzon12-Oct-07 6:03 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140415.2 | Last Updated 12 Oct 2007
Article Copyright 2007 by Vincent-Philippe Lauzon
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid