Obtaining Data as XML from SQL Server

  • Article

Microsoft SQL Server 2000 introduces support for XML functionality when retrieving data. To enable you to return an XML stream directly from Microsoft SQL Server 2000, the .NET Framework Data Provider for SQL Server SqlCommand object has the ExecuteXmlReader method. ExecuteXmlReader returns a System.Xml.XmlReader object populated with the results of the SQL statement specified for a SqlCommand. For more information about the XmlReader, see the XmlReader Class. ExecuteXmlReader can only be used with a statement that returns results as XML data, such as statements that include the SQL Server 2000 FOR XML clause, as shown in the following example.

Dim custCMD As SqlCommand = New SqlCommand("SELECT * FROM Customers FOR XML AUTO, ELEMENTS", nwindConn)

Dim myXR As System.Xml.XmlReader = custCMD.ExecuteXmlReader()
[C#]SqlCommand custCMD = new SqlCommand("SELECT * FROM Customers FOR XML AUTO, ELEMENTS", nwindConn);

System.Xml.XmlReader myXR = custCMD.ExecuteXmlReader();

The DataSet can also be used to write relational data as XML and can be synchronized with an XmlDataDocument to provide a real-time relational and hierarchical view of a single set of data in memory. For more information, see Populating a DataSet from a DataAdapter and XML and the DataSet.

If there is no need for an in-memory relational view of the data using the DataSet, the ExecuteXmlReader method is well suited for retrieving XML data, especially for large quantities of data. Because ExecuteXmlReader is a streaming API, it does not have to retrieve and cache all the data before exposing it to the caller, as would be the case if a DataSet were used to convert relational data into XML.

Closing the XmlReader

You should always call the Close method when you have finished using the XmlReader object. While an XmlReader is open, the Connection is in use exclusively by that XmlReader. You will not be able to execute any commands for the Connection, including creating another XmlReader or DataReader, until the original XmlReader is closed.

See Also

SqlCommand Class | XmlReader Class | Populating a DataSet from a DataAdapter | XML and the DataSet