Passing XML to Sql Server

One may want to pass an array of data to SQL Server. The most recent reason for me was so I could look for a specific record based on multiple criteria.

There are several ways to pass XML to SQL Server. One of the simplest ways to pass the data is as a string. However other options are available such as creating an SqlXML object from a list using the following C# function from

* Get an SqlXml representation of the list
* @param IEnumerable list: The list to convert
* @return SqlXml
public static SqlXml GetSqlXml(IEnumerable list)
    //We don't use 'using' or dispose or close the stream,
    //since it lives in the return variable
    MemoryStream stream = new MemoryStream();
    using (XmlWriter writer = XmlWriter.Create(stream))
        foreach (object obj in list)
            writer.WriteElementString("item", obj.ToString());
        stream.Position = 0;
        return new SqlXml(stream);


Another way to create the XML representation, in, is to use the following:

Dim xmlDoc As New System.Xml.XmlDocument
Dim table As System.Xml.XmlElement = xmlDoc.CreateElement("table")
For Each child As String In New String() {"s1", "s2", "s3"}
    Dim id As System.Xml.XmlElement = xmlDoc.CreateElement("id")
    id.innerText = child

Then pass the xmlDoc as a string. Once you have the parameter set up, you can define a stored procedure to accept an XML parameter such as the following:

CREATE PROCEDURE [dbo].[StoredProcedure1] ( @Criteria XML = NULL )

The last important piece is to parse the XML in SQL Server. If you have the following XML:


You can access the “id” nodes by using the following:

SELECT'.', 'varchar(50)') AS ID
FROM @Criteria.nodes('/table/id') AS r(id)