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 provides 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.

QuickStart

For an extended example of how to use the ExecuteXmlReader method to retrieve multiple rows of data as XML, see the QuickStart walkthrough, Walkthrough: Retrieving Multiple Rows as XML.

Using ExecuteXmlReader

The following code shows how to use the ExecuteXmlReader method.

SqlDatabase dbSQL = DatabaseFactory.CreateDatabase("EntLibQuickStartsSql") as SqlDatabase;

// Use "FOR XML AUTO" to have SQL return XML data.
string sqlCommand = "SELECT ProductID, ProductName FROM Products FOR XML AUTO";
DbCommand dbCommand = dbSQL.GetSqlStringCommand(sqlCommand);

XmlReader productsReader = null;
StringBuilder productList = new StringBuilder();

try
{
  productsReader = dbSQL.ExecuteXmlReader(dbCommand);

  // Iterate through the XmlReader and put the data into our results.
  while (!productsReader.EOF)
  {
    if (productsReader.IsStartElement()) 
    {
      productList.Append(productsReader.ReadOuterXml());
      productList.Append(Environment.NewLine);
    }
  }   
}
finally
{
  // Close the Reader.
  if (productsReader != null)
  {
    productsReader.Close();
  }
}

'Usage
Dim dbSQL As SqlDatabase = DirectCast(DatabaseFactory.CreateDatabase("EntLibQuickStartsSql"), SqlDatabase)

' Use "FOR XML AUTO" to have SQL return XML data.
Dim sqlCommand As String = "SELECT ProductID, ProductName FROM Products FOR XML AUTO"
Dim dbCommand As DbCommand = dbSQL.GetSqlStringCommand(sqlCommand)

Dim productsReader As XmlReader = Nothing
Dim productList As StringBuilder = New StringBuilder()

Try

  productsReader = dbSQL.ExecuteXmlReader(dbCommand)

  ' Iterate through the XmlReader and put the data into our results.
  While (Not productsReader.EOF)
    If (productsReader.IsStartElement()) Then
      productList.Append(productsReader.ReadOuterXml())
      productList.Append(Environment.NewLine)
    End If
  End While

Finally
  ' Close the Reader if there is no active transaction.
  If (Not productsReader Is Nothing And Transaction.Current Is Nothing) Then
    productsReader.Close()
  End If
End Try

Usage Notes

The connection must remain open while you read data from the XmlReader. The ExecuteXmlReader method of the SqlCommand object currently does not support the CommandBehavior.CloseConnection enumerated value, so you must explicitly close the connection when you finish with the reader but only if there is no active transaction. If the Transaction.Current static property is not null, it means that a TransactionScope instance is active. In this case, do not close the connection because the application block is using a shared connection for the database commands.

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.