|
FOR XML extends a SELECT statement by returning the relational query results in an XML format.
FOR XML operates in four different modes: RAW, AUTO, EXPLICIT, and PATH.
In RAW mode, a single row element is generated for each row in the result set, with each column in the result converted to an attribute within the element.
The syntax for using RAW mode is as follows:
FOR XML { RAW [ ('ElementName') ] }
[ [ , BINARY BASE64 ] [ , TYPE ]
[ , ROOT [ ('RootName') ] ]
[ , { XMLDATA | XMLSCHEMA
[ ('TargetNameSpaceURI') ]} ]
[ , ELEMENTS [ XSINIL | ABSENT ] ]
BINARY BASE64 When this option is selected, binary data is returned using Base64-encoded format.
TYPE When TYPE is designated, the query returns results in the XML data type.
ROOT [ ('RootName') ] Specifies the top-level element for the XML results.
XMLDATA When XMLDATA is used, XML-Data Reduced (XDR) schema is returned.
XMLSCHEMA [ ('TargetNameSpaceURI') ] When XMLSCHEMA is used, XSD in-line schema is returned with the data results.
ELEMENTS When ELEMENTS is used, columns are returned as sub-elements.
XSINIL In conjunction with ELEMENTS, empty elements are returned for NULL values.
ABSENT Specifies that in conjunction with ELEMENTS, elements are not created for NULL values (this behavior is the default).
The FOR XML AUTO mode creates XML elements in the results of a SELECT statement, and also automatically nests the data, based on the columns in the SELECT clause. AUTO shares the same options as RAW.
Reference from:
SQL Server 2005 T-SQL Recipes A Problem-Solution Approach
|