Obtaining XML Data from SQL Server 2000
Microsoft SQL Server 2000 introduced support for XML functionality when retrieving data. To enable you to return an XML stream directly from SQL Server 2000, the SqlCommand object of the .NET Framework Data Provider for SQL Server 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 command As SqlCommand = New SqlCommand( _
"SELECT * FROM Customers FOR XML AUTO, ELEMENTS", connection)
Dim reader As System.Xml.XmlReader = command.ExecuteXmlReader()
SqlCommand command = new SqlCommand(
"SELECT * FROM Customers FOR XML AUTO, ELEMENTS", connection);
System.Xml.XmlReader reader = command.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 Using XML in a 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.