Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


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

Cindy Rodriguez
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap