XmlReader to Retrieve Multiple Rows

XmlReader to Retrieve Multiple Rows

You can use the SqlCommand object to generate an XmlReader object, which provides forward-only, stream-based access to XML data. The command (usually a stored procedure) must generate an XML-based result set, which for SQL Server 2000 usually consists of a SELECT statement with a valid FOR XML clause. The following code fragment illustrates this approach:

public void RetrieveAndDisplayRowsWithXmlReader()

{

  using( SqlConnection conn = new SqlConnection(connectionString) )

  {;

    SqlCommand cmd = new SqlCommand("DATRetrieveProductsXML", conn );

    cmd.CommandType = CommandType.StoredProcedure;

try

  {

      conn.Open();

    XmlTextReader xreader = (XmlTextReader)cmd.ExecuteXmlReader();

    while ( xreader.Read() )

    {

      if ( xreader.Name == "PRODUCTS" )

      {

        string strOutput = xreader.GetAttribute("ProductID");

        strOutput += " ";

        strOutput += xreader.GetAttribute("ProductName");

        Console.WriteLine( strOutput );

      }

    }

    xreader.Close();  // XmlTextReader does not support IDisposable so it can't be

                      // used within a using keyword

  }

}

 

The preceding code uses the following stored procedure:

CREATE PROCEDURE DATRetrieveProductsXML

AS

SELECT * FROM PRODUCTS

FOR XML AUTO

GO