HOW TO: Use the ExecuteXmlReader Method of the SqlCommand Class in Visual C# .NET

You can use the ExecuteXmlReader method of the System.Data.SqlClient.SqlCommand class to load the results of SQL Extensible Markup Language (XML) queries from Microsoft SQL Server into a System.Xml.XmlReader object. You can use this method to run server-side for XML queries and to run queries that return well-formed XML data as text.

NOTE: This method is not available in the OLE DB .NET managed provider (it is specific to the SQL Server .NET managed provider) and does not work with a SQL 7.0 database.

Requirements

The following list outlines the recommended hardware, software, network infrastructure, and service packs that you need:

• Microsoft Windows NT 4.0 Server, Windows 2000 Server, or another Microsoft operating system that can host SQL Server 2000
• Microsoft Visual Studio .NET

This article assumes that you are familiar with XML and SQL XML queries.

Create and Run the Sample Code

1. Create a new Windows application in Visual C# .NET, and then add a Button control to Form1.
2. Add the following code to the Click event of the Button:

   try
   
   {
   
               System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection();
   
               cn.ConnectionString="data source=(local);initial catalog=pubs;User ID=myuser;Password=mypassword";
   
               cn.Open();
   
               
   
               System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
   
               cmd.Connection = cn;
   
               cmd.CommandText = "SELECT * FROM authors FOR XML AUTO, XMLDATA";
   
               System.Xml.XmlReader xmlr = cmd.ExecuteXmlReader();
   
                                       
   
               xmlr.Read();
   
               while(xmlr.ReadState != System.Xml.ReadState.EndOfFile)
   
               {
   
                           System.Diagnostics.Debug.WriteLine(xmlr.ReadOuterXml());
   
               }                       
   
    
   
               MessageBox.Show("Reached the end. Check the output window.");
   
   }
   
   catch (Exception ex)
   
   {
   
               MessageBox.Show(ex.Message);
   
   }
   					

3. Modify the settings in the connection string as appropriate for your environment.
4. Run the program in debug mode (from Start or press F5), and then click the button. The Xmlr XmlReader object contains the results of the SQL XML query. The Output window contains the contents of the XmlReader object.