Walkthrough: Retrieving Multiple Rows As XML

Article
05/09/2013

Retired Content
This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.
The latest Enterprise Library information can be found at the Enterprise Library site.

This walkthrough demonstrates how to retrieve XML data from a SQL Server database.

To reproduce the demonstration

Configure the database. For the necessary steps, see "QuickStart Configuration" in Data Access QuickStart.
Create the database (when you are not using the Unity Integration approach). The following code uses the factory to create a SqlDatabase object that has the default configuration.
```
SqlDatabase dbSQL = DatabaseFactory.CreateDatabase() as SqlDatabase;
```
```
'Usage
Dim dbSQL As SqlDatabase = DirectCast(DatabaseFactory.CreateDatabase(), SqlDatabase)
```

Create the command by adding the following code. It creates a DbCommand used with a SQL string.

string sqlCommand = "Select ProductID, ProductName, CategoryID, UnitPrice, " 
                  + "LastUpdate From Products FOR XML AUTO";
DbCommand dbCommand = dbSQL.GetSqlStringCommand(sqlCommand);

'Usage
Dim sqlCommand As String = "Select ProductID, ProductName, CategoryID, " _
                         & " UnitPrice, LastUpdate From Products FOR XML AUTO"

Dim dbCommand As DbCommand = dbSQL.GetSqlStringCommand(sqlCommand)

Add the following code to call ExecuteXmlReader and process the results.

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

try

{
  productsReader = dbSQL.ExecuteXmlReader(dbCommand);

  while (!productsReader.EOF)
  {
    if (productsReader.IsStartElement()) 
    {
      productList.Append(productsReader.ReadOuterXml());
      productList.Append(Environment.NewLine);
    }
  }   
}

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

Try

  productsReader = dbSQL.ExecuteXmlReader(dbCommand)

  While (Not productsReader.EOF)
    If (productsReader.IsStartElement()) Then
      productList.Append(productsReader.ReadOuterXml())
      productList.Append(Environment.NewLine)
    End If
  End While

Close the XmlReader and Connection by adding the following code.

finally
{
  // Close the Reader.
  if (productsReader != null)
  {
    productsReader.Close();
  }

  // Explicitly close the connection. The connection is not closed
  // when the XmlReader is closed.
  if (dbComman.Connection != null)
  {
    dbCommand.Connection.Close();
  }       
}

'Usage
Finally
  ' Close the Reader.
  If (Not productsReader Is Nothing) Then
    productsReader.Close()
  End If

  ' Explicitly close the connection. The connection is not closed
  ' when the XmlReader is closed.
  If (Not dbCommand.Connection Is Nothing) Then
    dbCommand.Connection.Close()
  End If
End Try

Walkthrough: Retrieving Multiple Rows As XML

Additional resources