Click here to Skip to main content
15,867,308 members
Articles / Database Development / SQL Server
Article

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

Rate me:
Please Sign up or sign in to vote.
4.64/5 (11 votes)
12 Oct 20073 min read 132.6K   539   53   14
Using XML data types in SQL Server 2005, we can pass an array of values from an application server to the database.

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:

C#
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:

C#
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:

XML
<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:

C#
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:

SQL
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


Written By
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 Pin
koche01216-Jul-09 5:22
koche01216-Jul-09 5:22 
GeneralRe: Nice solution Pin
Vincent-Philippe Lauzon16-Jul-09 5:25
Vincent-Philippe Lauzon16-Jul-09 5:25 
Questionhow to insert data using this xml Pin
ranger301014-Jan-09 2:35
ranger301014-Jan-09 2:35 
GeneralYou are a better man than me. Pin
S432**%$20-Jun-08 9:58
S432**%$20-Jun-08 9:58 
GeneralXML in stored proc [modified] Pin
sujit.patil2-Mar-08 20:24
sujit.patil2-Mar-08 20:24 
GeneralRe: XML in stored proc Pin
Vincent-Philippe Lauzon3-Mar-08 7:36
Vincent-Philippe Lauzon3-Mar-08 7:36 
QuestionProblem while Hierarchy data Pin
sacp.net6-Nov-07 11:32
sacp.net6-Nov-07 11:32 
AnswerRe: Problem while Hierarchy data Pin
Vincent-Philippe Lauzon7-Nov-07 2:45
Vincent-Philippe Lauzon7-Nov-07 2:45 
GeneralRe: Problem while Hierarchy data Pin
sacp.net7-Nov-07 6:28
sacp.net7-Nov-07 6:28 
Thanks a tons....Its works kool.......this gone help me lots....and I others too....Smile | :)

Cheers!! njoy your vacations...
--
Sachin
GeneralXML schema for validating the XML input Pin
bertkid12-Oct-07 9:19
bertkid12-Oct-07 9:19 
GeneralRe: XML schema for validating the XML input Pin
Vincent-Philippe Lauzon12-Oct-07 9:42
Vincent-Philippe Lauzon12-Oct-07 9:42 
GeneralRe: XML schema for validating the XML input Pin
bertkid17-Oct-07 8:32
bertkid17-Oct-07 8:32 
QuestionWhy? Pin
Gogza12-Oct-07 5:16
Gogza12-Oct-07 5:16 
AnswerRe: Why? PinPopular
Vincent-Philippe Lauzon12-Oct-07 6:03
Vincent-Philippe Lauzon12-Oct-07 6:03 

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

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