Retrieving Data Using the 'FOR XML' Clause

Now that you have your virtual directory ready, understand the basics of XML, and know a little bit about SQL Server 2000's brand of XML, you're ready to execute queries that return XML.

The select statement has been extended to include an optional FOR XML clause that produces XML directly from the SQL Server 2000 query engine. This powerful, highly anticipated feature is a major boon to developers who have previously been manually translating the rowsets that queries return into XML documents.

The syntax for retrieving XML using FOR XML is as follows:

select statement [ FOR XML mode [, XMLDATA] [, ELEMENTS][, BINARY BASE64] ] 

Mode can be one of the following: RAW, AUTO, or EXPLICIT. It determines the structure of the XML-formatted query results.


Multiple XML rowsets might be returned from a single stored procedure having multiple select statements, each using a different XML mode. The mode you use applies only to the results of the currently executing select statement and not to the results of any subsequent select statements.

    Part III: SQL Server Administration
    Part IV: Transact-SQL
    Part V: SQL Server Internals and Performance Tuning
    Part VI: Additional SQL Server Features