8.5. Reading XML Data Directly from SQL Server
Problem
You need to read XML data directly from the SQL Server.
Solution
Use the FOR XML
clause in the stored procedure or
SQL statement.
The C# code is shown in Example 8-8.
Example 8-8. File: ReadXmlDirectForm.cs
// Namespaces, variables, and constants using System; using System.Configuration; using System.Xml; using System.Data; using System.Data.SqlClient; // . . . // Select statement to read XML directly. String sqlText = "SELECT * FROM Orders FOR XML AUTO, XMLDATA"; // Create the connection. SqlConnection conn = new SqlConnection( ConfigurationSettings.AppSettings["Sql_ConnectString"]); conn.Open( ); // Create the command. SqlCommand cmd = new SqlCommand(sqlText, conn); // Read the XML data into a XML reader. XmlReader xr = cmd.ExecuteXmlReader( ); // Read the data from the XML reader into the DataSet. DataSet ds = new DataSet( ); ds.ReadXml(xr, XmlReadMode.Fragment); xr.Close( ); conn.Close( ); xmlTextBox.Text = ds.GetXml( );
Discussion
SQL Server 2000 introduced support for retrieving data in XML format
using the FOR
XML
clause. The
.NET SQL Server data provider SqlCommand
object
has an ExecuteXmlReader( )
that allows you to
retrieve an XML stream directly from SQL Server, where it returns an
XmlReader
that contains the results of the SQL
query. The ExecuteXmlReader( )
method can only be
used with SQL statements that return XML data, such as those with a
FOR XML
clause. The ExecuteXmlReader( )
method can also be used to return ...
Get ADO.NET Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.