My blog share the my c# knowledge to others and get response from others.

Tuesday, December 13, 2011

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