Microsoft® SQL Server 2012 Unleashed

FOR XML and the xml Data Type

By default, the results of any FOR XML query (using all four modes) is streamed to output as a one-column/one-row dataset with a column named XML_F52E2B61-18A1-11d1-B105-00805F49916B of type nvarchar(max). (In SQL Server 2000, this was a stream of XML split into multiple varchar(8000) rows.)

One of the biggest limitations of SQL Server 2000’s XML production was the inability to save the results of a FOR XML query to a variable or store it in a column directly without using some middleware code to first save the XML as a string and then insert it back into an ntext or nvarchar column and then select it out again.

Today, SQL Server 2012 natively supports column storage of XML, using the xml data type. Be sure to read ...