Integration with the XML Data Type
With the introduction of the XML data type, we wanted to also give FOR XML the ability to generate an instance of XML directly (more precisely, it generates a single row, single column rowset where the cell contains the XML data type instance).
Because of the backwards-compatibility considerations outlined above, we added a new TYPE directive to generate the result as XML. For example,
Copy
SELECT * from Customers FOR XML AUTO, TYPE
returns the Customers elements as an XML data type instance, instead of the nvarchar(max) instance that would have been the case without the TYPE directive.
This result is guaranteed to conform to the well-formedness constraints provided by the XML data type. Since the result is an XML data type instance, you can also use XQuery expressions to query and reshape the result. For example, the following expression retrieves the Customer contact name into a new Person element.
Copy
SELECT (SELECT * from Customers FOR XML AUTO, TYPE).query(
'<doc>{
for $c in /Customers
return
<Person name="{data($c/@ContactName)}"/>
}</doc>')
returns (only first elements shown),
Copy
<doc>
<Person name="Maria Anders" />
<Person name="Ana Trujillo" />
<Person name="Antonio Moreno" />
...
</doc>
Assigning FOR XML Results
Since FOR XML queries now return assignable values, the result of a FOR XML query can be assigned to a variable, or inserted into a column.
Copy
DECLARE @cust XML;
SET @cust = (SELECT * from Customers FOR XML AUTO, TYPE)
CREATE TABLE T(i int, x XML)
INSERT INTO T SELECT 1, (SELECT * from Customers FOR XML AUTO, TYPE)
Nesting of FOR XML Expressions
FOR XML, in SQL Server 2005, recognizes XML data type columns, and will inline them as sub-elements. Thus, we can nest FOR XML queries to generate hierarchies, instead of having to rely on the AUTO mode heuristic, or writing an EXPLICIT mode query.
Let's look
