SQL Server – Pass a List from your ASP.NET Code to a Stored Procedure using XML

Sometimes while developing a tool, a developer needs to pass a list of values with a variable size to their database. There are several ways of doing so, one of which involves passing the list in XML format.

The introduction of SQL Server 2005 offered the use of XML datatypes, which had not existed in previous versions of SQL Server. The good thing about the XML datatype is that it is not just good for storing information in XML format, but also has advantages such as being fully query-able similar to a database table.

As you look through this example, please feel free to check out other articles I have written for more SQL Tips

Here are the three main ways of passing lists of parameters to the different versions of SQL Server

A big benefit of the XML datatype is the ability to pass lists of items to stored procedures as XML format parameters. In SQL Server 2000 one would have to pass a list of parameters as a String that would then need to be parsed into its components in the Stored Procedure. This a tedious process that could easily introduce parsing errors. Subsequent versions of SQL Server have added extra ways in which to pass information lists to Stored Procedures:

1. As a character-separated list contained in a String (VARCHAR) variable – SQL Server 2000 +
2. As an XML variable – SQL Server 2005 +
3. As a table variable – SQL Server 2008 +

As you can see from the list above, the methods of passing lists to stored procedures have increased in number over the last few iterations of SQL Server. The catch of course is that as a developer you will need to adapt your code to whichever instance of SQL Server your project is based on.

Example XML List

In the example below I will review how to pass a list of values from your ASP.NET code to SQL Server via XML. Here is what the XML looks like for this example.

<ItemList>
<data>1234</data>
<data>5678</data>
<data>9012</data>
<data>3456</data>
<data>7890</data>
</ItemList>

Passing the XML Parameters as a String to the Stored Procedure

In the example below , we will generate the XML structure shown above from a list of parameters that have been passed to us in the URL of an HTTP GET request such as:

http: / / MySite / MyTestWebPage.aspx?p1=1234&P2=5678&P3=9012&P4=3456&P5=7890

So as you can see we pass the parameters as a list to the Web page called MyTestWebPage.aspx. The actual names of the parameters doesn’t really matter so since the GET request is limited to 256K, we simply call them P1,P2,P3… PX

Parsing the URL Parameters into an XML-Formatted String

The next step is to pass the entire URL to a function that will separate the parameters and input them into an XML-structured String value (as pictured in the XML example above). Here is the function:

Function xmlFromURLParameters(ByVal strURL As String) As String
 Dim returnValue As String = "<ItemList>"
 If Not Page.IsPostBack Then
 Dim strProcessString As String = strURL.Substring(strURL.IndexOf("?") + 1, strURL.Length - (strURL.IndexOf("?") + 1))
 Dim sarParams() As String = strProcessString.Split("&")
 For Each strItem As String In sarParams
 Dim sarItemValuePair() As String = strItem.Split("=")
 Dim strItemValue As String = sarItemValuePair(1)
 returnValue &= "<data>" & strItemValue & "</data>"
 Next
 End If
 returnValue &= "</ItemList>"
 xmlFromURLParameters = returnValue
 End Function

As you can see, the parsing function first separates the domain and page from the parameters using a Substring function:

Dim strProcessString As String = strURL.Substring(strURL.IndexOf("?") + 1, strURL.Length - (strURL.IndexOf("?") + 1))

Then the portion of the string containing the parameters is split into a String array using the Split function

Dim sarParams() As String = strProcessString.Split("&")

Finally, the code then loops through the String array of the parameters and fits them together in the XML structure we intend to pass to our Stored Procedure. The parameter value is separated from the parameter id within the loop using another String Split function, but this time based on the = sign:

Dim sarItemValuePair() As String = strItem.Split("=")

Calling the SQL Server Stored Procedure Passing in the XML Parameter List

The next step is to call our Stored Procedure and pass in our list of parameters as the String we get from our custom xmlFromURLParameters() function. I won’t go into the full code, but the connection will look something like the following:

If Not Page.IsPostBack Then
 Dim strParamXML As String = xmlFromURLParameters()
 Dim conn As SqlConnection
 Dim dadapter As New SqlDataAdapter()
 Dim dsDataSet As New DataSet()
 Dim strCredentials As String = ConfigurationManager.ConnectionStrings("MyCredentials").ConnectionString
 conn = New SqlConnection(strCredentials)
 conn.Open()
 Dim cmd As New SqlCommand("MyStoredProcedure", conn)
 cmd.CommandType = CommandType.StoredProcedure
 cmd.Parameters.AddWithValue("ParameterList", strParamXML)
 dadapter.SelectCommand = cmd
 dadapter.Fill(dsDataSet)

The code above assumes that you are receiving output back from your Stored Procedure and so it fills a DataSet with the results. There isn’t anything too interesting with this database connection, except for setting the parameter to the Stored Procedure which accepts an XML datatype parameter. In our case, we can simply pass the parameter as a String formatted value containing valid XML:

cmd.Parameters.AddWithValue("ParameterList", strParamXML)

The Stored Procedure

Below I am providing example code for what your Stored Procedure could look like.

USE [MyDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[MyStoredProcedure]
@ParameterList XML
AS
BEGIN
SET NOCOUNT ON;
 SELECT FirstName, LastName, Address
 FROM MyTable a
 WHERE a.[UserId] in (
    SELECT SearchItems.ItemToSearch.value('.','VARCHAR(50)')
    FROM @ParameterList.nodes('/ItemList/data') AS SearchItems(ItemToSearch)
 ) 
 order by FirstName desc
END

As you can see from this example, the procedure accepts a single parameter, which is our XML data-typed list of values. Note that we did not need to pass anything other than a String parameter to SQL Server from our ASP.NET code in the connection. As long as the XML contained in the String is valid, SQL Server will accept the value as a valid XML Parameter to the Stored Procedure.

Another interesting point is to see how the XML parameter is then used as a list of valid values in the Stored Procedure itself. The Stored Procedure queries a table in the fictional database for a list of users based on the XML Parameter. This is done by limiting the results of the main table query using a sub-query of the XML.

Summary

I hope the example in this article has been useful. As always please feel free to leave any comments or suggestions in the section below.