Constructing XML Using FOR XML
A SELECT query returns results as a rowset. You can optionally retrieve formal results of a SQL query as XML by specifying the FOR XML clause in the query. In a FOR XML clause, you specify one of these modes:
- RAW
- AUTO
- EXPLICIT
- PATH
The RAW mode generates a single <row> element per row in the rowset that is returned by the SELECT statement. You can generate XML hierarchy by writing nested FOR XML queries.
The AUTO mode generates nesting in the resulting XML by using heuristics based on the way the SELECT statement is specified. You have minimal control over the shape of the XML generated. The nested FOR XML queries can be written to generate XML hierarchy beyond the XML shape that is generated by AUTO mode heuristics.
The EXPLICIT mode allows more control over the shape of the XML. You can mix attributes and elements at will in deciding the shape of the XML. It requires a specific format for the resulting rowset that is generated because of query execution. This rowset format is then mapped into XML shape. The power of EXPLICIT mode is to mix attributes and elements at will, create wrappers and nested complex properties, create space-separated values (for example, OrderID attribute may have a list of order ID values), and mixed contents.
However, writing EXPLICIT mode queries can be cumbersome. You can use some of the new FOR XML capabilities, such as writing nested FOR XML RAW/AUTO/PATH mode queries and the TYPE directive, instead of using EXPLICIT mode to generate the hierarchies. The nested FOR XML queries can produce any XML that you can generate by using the EXPLICIT mode. For more information, see Nested FOR XML Queries and TYPE Directive in FOR XML Queries.
The PATH mode together with the nested FOR XML query capability provides the flexibility of the EXPLICIT mode in a simpler manner.
These modes are in effect only for the execution of the query for which they are set. They do not affect the results of any subsequent queries.
See Also
Reference
Basic Syntax of the FOR XML Clause
Guidelines for Using the FOR XML Clause
Using RAW Mode
Using AUTO Mode
Using EXPLICIT Mode
New FOR XML Features
Querying XML Using OPENXML
Using FOR XML and OPENXML to Publish and Process XML Data
Concepts
Using PATH Mode
Sample XML Applications
Adding Namespaces Using WITH XMLNAMESPACES