This documentation is archived and is not being maintained.

SqlCommand.ExecuteXmlReader Method

Sends the CommandText to the Connection and builds an XmlReader object.

[Visual Basic]
Public Function ExecuteXmlReader() As XmlReader
[C#]
public XmlReader ExecuteXmlReader();
[C++]
public: XmlReader* ExecuteXmlReader();
[JScript]
public function ExecuteXmlReader() : XmlReader;

Return Value

An XmlReader object.

Exceptions

Exception Type Condition
SqlException An exception occurred while executing the command against a locked row. This exception is not generated when using Microsoft .NET Framework version 1.0.

Remarks

The CommandText property usually specifies a Transact-SQL statement with a valid FOR XML clause. However, CommandText can also specify a statement that returns ntext data containing valid XML.

A typical ExecuteXmlReader query can be formatted as in the following C# example:

SqlCommand mySqlCommand = new SqlCommand("select * from customers FOR XML AUTO, XMLDATA", mySqlConnection);
Note   This type of query only works with Microsoft SQL Server 2000 or later. For details see Guidelines for Using the FOR XML Clause in SQL Server Books Online.

While the XmlReader is in use, the associated SqlConnection is busy serving the XmlReader. While in this state, no other operations can be performed on the SqlConnection other than closing it. This is the case until the Close method of the XmlReader is called.

Example

[Visual Basic, C#, C++] The following example creates a SqlCommand and then executes it using ExecuteXmlReader. The example is passed a string that is a Transact-SQL FOR XML SELECT statement, and a string to use to connect to the data source.

[Visual Basic] 
Public Sub CreateMyXmlReader(myXmlQuery As String, myConnection As SqlConnection)
    Dim myCommand As SqlCommand = New SqlCommand(myXmlQuery, myConnection)

    Try
      myConnection.Open()

      Dim myXmlReader As System.Xml.XmlReader = myCommand.ExecuteXmlReader()

      ' Always close the XmlReader when finished.
      myXmlReader.Close()

    Catch e As Exception
      Dim log As System.Diagnostics.EventLog = New System.Diagnostics.EventLog()
      log.Source = "My Application"
      log.WriteEntry(e.ToString())
      Console.WriteLine("Exception of type {0} occurred.", e.GetType().ToString())
    Finally
      myConnection.Close()
    End Try
End Sub

[C#] 
public void CreateMyXmlReader(string myXmlQuery, SqlConnection myConnection) 
 {
    SqlCommand myCommand = new SqlCommand(myXmlQuery, myConnection);
    try
    {
     myConnection.Open();

     System.Xml.XmlReader myXmlReader = myCommand.ExecuteXmlReader();

     // Always close the XmlReader when finished.
     myXmlReader.Close();
    }
    catch(Exception e)
    {
      System.Diagnostics.EventLog log = new System.Diagnostics.EventLog();
      log.Source = "My Application";
      log.WriteEntry(e.ToString());
      Console.WriteLine("Exception of type {0} occurred.", e.GetType());
    }
    finally
    {
      myConnection.Close();
    }
 }

[C++] 
public:
    void CreateMyXmlReader(String* myXmlQuery, SqlConnection* myConnection)
    {
        SqlCommand* myCommand = new SqlCommand(myXmlQuery, myConnection);
        try
        {
            myConnection->Open();

            System::Xml::XmlReader* myXmlReader = myCommand->ExecuteXmlReader();

            // Always close the XmlReader when finished:
            myXmlReader->Close();
        }
        catch (Exception* e)
        {
            System::Diagnostics::EventLog* log = new System::Diagnostics::EventLog();
            log->Source = S"My Application";
            log->WriteEntry(e->ToString());
            Console::WriteLine(S"Exception of type {0} occurred.", e->GetType());
        }
        myConnection->Close();
    };

[JScript] No example is available for JScript. To view a Visual Basic, C#, or C++ example, click the Language Filter button Language Filter in the upper-left corner of the page.

Requirements

Platforms: Windows 98, Windows NT 4.0, Windows Millennium Edition, Windows 2000, Windows XP Home Edition, Windows XP Professional, Windows Server 2003 family, .NET Compact Framework

See Also

SqlCommand Class | SqlCommand Members | System.Data.SqlClient Namespace

Show: