Using XmlReader

for RuBoard

Using `XmlReader`

In Hour 8, "Using the DataReader and DataAdapter," you learned how to use the DataReader object to handle a stream of data returned from Microsoft SQL Server. The XmlReader object is a close cousin of the DataReader object and is used in much the same way, with XML. In the next few sections, you'll learn about some new features of Microsoft SQL Server 2000 and the XmlReader object.

Using `XmlReader` with SQL Server 2000

Microsoft SQL Server 2000 ships with numerous improvements and features not present in previous versions of SQL Server. One of my favorite new additions found in SQL Server 2000 is the ability to retrieve data in native XML format. If you add the keywords "FOR XML" to the end of your database query, the server will automatically send the results of your query in XML form. By being able to speak directly in XML, SQL Server 2000 is much better at cross-platform operations.

The example in Listing 10.5 demonstrates how to retrieve the results of a database query in XML form and then write that XML information to a file. However, as you've learned in this hour, that XML data could be bound to a Web control, saved to a file, or even transmitted to a remote client using Web Services.

There are three modes you can use to retrieve the XML information from SQL Server 2000:

FOR XML AUTO ” The query returns nested XML elements. The elements are organized based on the order of the fields and tables specified in your query.
FOR XML EXPLICIT ” The query enables you to shape the XML tree that is returned in detail. This is by far the most complex of the three modes.
FOR XML RAW ” The query returns XML elements surrounded by the "row" prefix. Table columns are represented as attributes.

Listing 10.5 Retrieving XML Directly from a Microsoft SQL Server 2000 Database in C#

 <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <HTML> <HEAD>     <LINK rel="stylesheet" type="text/css" href="Main.css">     <!-- End Style Sheet -->     <script language="C#" runat="server" >       void Page_Load(Object Source, EventArgs E)       {          SqlConnection conn = new SqlConnection("Initial " +                              "Catalog=Northwind;Server=(local);UID=sa;PWD=;");          SqlCommand cmd = new SqlCommand("SELECT * FROM Customers " +                                           "FOR XML AUTO", conn);          DataSet dsCustomers = new DataSet();          conn.Open();          System.Xml.XmlReader xmlReader = cmd.ExecuteXmlReader();          conn.Close();          StringBuilder strBuilder = new StringBuilder();          while( xmlReader.Read() )          {             while( xmlReader.MoveToNextAttribute() )             {                 strBuilder.Append(xmlReader.ReadString() + "<br><br>");             }          }          lblOutput.Text = strBuilder.ToString();       }     </script> </HEAD> <body>     <form runat="server">         XML Output:<br>         <asp:label id="lblOutput" runat="server"/>     </form> </body> </html>

The example in Listing 10.5 uses the FOR XML clause at the end of the database query to retrieve XML data instead of a standard resultset. The XML stream returned from SQL Server is placed into the XmlReader object using the ExecuteXmlReader() method of the Command object. Then, the XmlReader is used to return pieces of the XML data using its MoveToNextAttribute() and ReadString() methods .

for RuBoard

Using XmlReader

Using XmlReader with SQL Server 2000

Listing 10.5 Retrieving XML Directly from a Microsoft SQL Server 2000 Database in C#

Using `XmlReader`

Using `XmlReader` with SQL Server 2000