Average Rating: 4.1/5 | Rate this item | 10 users have rated this item.

Expertise: Intermediate
Language: SQL

November 11, 2008

Create XML from an Array

Passing arrays as arguments to stored procedures is not currently possible in SQL Server, but you can use XML to accomplish this. To do this, you need to create an XML string using your preferred language (C#, VB, etc.). You can use the following function to convert an array to an XML string (which you can then pass to the stored procedure). This example uses C#, but you can use the same logic with other languages:

    protected void Page_Load(object sender, EventArgs e)
    {
        Object[] test = { 1, "item 2", 3, "item 4", 5, "item 6" };
        Response.Write(ConvertToXML(test, "params", "param"));
    }

    protected string ConvertToXML(Object[] args, string rootName, string elemName)
    {
        string xmlStr = "<" + rootName + ">";

        foreach (Object arg in args)
        {
            xmlStr += "<" + elemName + ">" + arg.ToString() +
                      "</" + elemName + ">";
        }

        xmlStr += "</" + rootName + ">";

        return xmlStr;
    }

Using the test case in the Page_Load method provided above as an example, the output XML would look like the following (but not as neatly formatted):

<params>
    <param>1</param>
    <param>item 2</param>
    <param>3</param>
    <param>item 4</param>
    <param>5</param>
    <param>item 6</param>
</params>

You can now pass that string to a SQL Server stored procedure or to other functions.