Retrieving Multiple Rows as XML

An example of where you may want to use XML data is within an e-commerce application that allows clients to request a product catalog that is in XML format.

Typical Goals

In this scenario, the goal is to retrieve data in XML format from a SQL Server database. You must provide a method that queries the database to obtain product catalog information and then returns the data to the caller as an XmlReader.

Solution

SQL Server 2000 and later provide XML support and allows you to retrieve XML data from a database. For example, you can use the FOR XML clause to retrieve XML fragments (that is, XML documents that have no root element) from the database. The SqlDatabase class provides the ExecuteXmlReader method. This method returns an XmlReader object that provides forward-only access to a stream of XML data. This approach assumes that the executed command contains a Transact-SQL statement that includes a valid FOR XML clause.

Using ExecuteXmlReader

The following code shows how to use the ExecuteXmlReader method. It assumes that you have resolved an instance of the SqlDatabase class and stored a reference in the variable named dbSQL. For more information on instantiating objects, see Creating and Referencing Enterprise Library Objects.

// Use "FOR XML AUTO" to have SQL return XML data.
string sql = "SELECT ProductID, ProductName FROM Products FOR XML AUTO";
DbCommand cmd = dbSQL.GetSqlStringCommand(sql);
IEnumerable<string> productList;

using(var reader = dbSQL.ExecuteXmlReader(cmd))
{
  if (reader.IsStartElement())
  {
   var root = (XElement)XNode.ReadFrom(reader);
   productList = root.Elements("Products")
                     .Attributes("ProductName")
                     .Select(a => a.Value).ToArray();
  }
}

'Usage
' Use "FOR XML AUTO" to have SQL return XML data.
Dim sql As String = "SELECT ProductID, ProductName FROM Products FOR XML AUTO"
Dim cmd As DbCommand = dbSQL.GetSqlStringCommand(sql)
Dim productList As IEnumerable(Of String)

Using reader = dbSQL.ExecuteXmlReader(cmd)
  If reader.IsStartElement() Then
    Dim root = DirectCast(XNode.ReadFrom(reader), XElement)
    productList = root.Elements("Products") _
                      .Attributes("ProductName") _
                      .Select(Function(a) a.Value).ToArray()
  End If
End Using

Usage Notes

The connection must remain open while you read data from the XmlReader. You must ensure that you correctly close and dispose the XmlReader after use. The recommended approach is the using construct, as shown in the example above.

Alternatively, you can call one of the ExecuteDataSet methods to retrieve a DataSet object, because this object allows the data to be manipulated and accessed as XML. However, using the SQL Server XML support and the FOR XML clause provides a great deal of flexibility because it allows you to determine element names, whether an element or attribute-centric schema should be used, whether a schema should be returned with the XML data, and so on. It also means that you avoid the performance impact associated with creating a DataSet and caching the data.