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;
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
SELECT * FROM PRODUCTS
FOR XML AUTO
To retrieve XML data with an XmlReader
- Create a SqlCommand object to invoke a stored procedure that generates an XML result set (for example, using the FOR XML clause on the SELECT statement). Associate the SqlCommand object with a connection.
- Call the ExecuteXmlReader method of the SqlCommand object and assign the results to a forward-only XmlTextReader object. This is the fastest type of XmlReader object that you should use when you do not require any XML-based validation of the returned data.
- Read the data by using the Read method of the XmlTextReader object.