Visual Basic 2008 Recipes: A Problem-Solution Approach

8.8. Obtain an XML Document from a SQL Server Query

Problem

You need to execute a query against a SQL Server 2000 (or later) database and retrieve the results as XML.

Solution

Specify the FOR XML clause in your SQL query to return the results as XML. Execute the command using the ExecuteXmlReader method of the System.Data.SqlClient.SqlCommand class, which returns a System.Xml.XmlReader object through which you can access the returned XML data.

How It Works

SQL Server 2000 (and later versions) provides direct support for XML. You simply need to add the clause FOR XML AUTO to the end of a SQL query to indicate that the results should be returned as XML. By default, the XML representation is not a full XML document. Instead, it simply returns the ...