How To Use the ExecuteXmlReader Method of the SqlCommand Class in Visual Basic .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 only 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 data 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, Microsoft 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 Basic .NET, and then add a Button control to Form1.
2. Add the following code to the Click event of the Button:

           Try
               Dim cn As New System.Data.SqlClient.SqlConnection()
               cn.ConnectionString = _
               "data source=(local);initial catalog=pubs;" & _
               "User ID=myuser;Password=mypassword"
               cn.Open()
   
               Dim cmd As New System.Data.SqlClient.SqlCommand()
               cmd.Connection = cn
               cmd.CommandText = "SELECT * FROM authors FOR XML AUTO, XMLDATA"
   
               Dim xmlr As System.Xml.XmlReader
               xmlr = cmd.ExecuteXmlReader()
               xmlr.Read()
               Do While xmlr.ReadState <> Xml.ReadState.EndOfFile
                   System.Diagnostics.Debug.WriteLine(xmlr.ReadOuterXml())
               Loop
               MessageBox.Show("Reached the end. Check the output window.")
   
           Catch ex As Exception
               MessageBox.Show(ex.Message)
           End Try
   					

3. Modify the settings in the connection string as necessary for your environment.
4. Run the program in debug mode (when you start or by pressing 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.