Reading XML Direct From SQL Server With An XmlReader
Reading XML Direct From SQL Server With An XmlReader
Connection string:
SELECT command:
void Page_Load(Object sender, EventArgs e)
{
// get connection string from ..\global\connect-strings.ascx user control
string strConnect = ctlConnectStrings.SQLConnectionString;
outConnect.InnerText = strConnect; // and display it
// specify the SELECT statement to extract the data as an
// XML document using SQL Server 2000's "FOR XML" option
string strSelect = "SELECT * FROM BookList WHERE ISBN LIKE '1861003%' FOR XML AUTO";
outSelect.InnerText = strSelect; // and display it
// create a new StringBuilder to hold the results
StringBuilder objStrBuilder = new StringBuilder();
//try
{
// create a new Connection object using the connection string
SqlConnection objConnect = new SqlConnection(strConnect);
// create a new Command using the connection object and select statement
SqlCommand objCommand = new SqlCommand(strSelect, objConnect);
// declare a variable to hold an XmlTextReader object
XmlTextReader objXTReader;
// open the connection to the database
objConnect.Open();
// execute the SQL statement against the command to fill the XmlReader
objXTReader = (XmlTextReader)objCommand.ExecuteXmlReader();
// read the first result to initialize the reader
objXTReader.Read();
// and then read remainder into the StringBuilder as well
objStrBuilder.Append(objXTReader.GetRemainder().ReadToEnd());
// close the XmlReader and Connection
objXTReader.Close();
objConnect.Close();
}
/*
catch (Exception objError)
{
// display error details
outError.InnerHtml = "* Error while accessing data."
+ objError.Message + "" + objError.Source;
return; // and stop execution
}
*/
// display the results as Text to show XML elements
outError.InnerText = objStrBuilder.ToString();
}
