RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: SQL
Expertise: Intermediate
Nov 12, 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):

    <param>item 2</param>
    <param>item 4</param>
    <param>item 6</param>
You can now pass that string to a SQL Server stored procedure or to other functions.

Cindy Rodriguez
Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date