CREATE TABLE #tab(col INT,x XML );
INSERT INTO #tab(col,x) VALUES (1,NULL),(2,(3,'<a>x</a>');

SELECT 'Simple XML' AS description,name,system_type_name
FROM sys.dm_exec_describe_first_result_set(
   N'SELECT col
     FROM #tab
     FOR XML AUTO',NULL,0)  
UNION ALL
SELECT 'Wrapped with subquery',system_type_name
FROM sys.dm_exec_describe_first_result_set(
   N'SELECT(SELECT col
            FROM #tab
            FOR XML AUTO) AS wrapped_subquery',0)
UNION ALL 
SELECT 'XML column',system_type_name
FROM sys.dm_exec_describe_first_result_set(
   N'SELECT x FROM #tab ',0)
UNION ALL
SELECT 'Casted XML',system_type_name
FROM sys.dm_exec_describe_first_result_set(
   N'SELECT CAST(''<o>O</o>'' AS XML) AS x',0)
UNION ALL
SELECT 'Wrapped Casted XML',system_type_name
FROM sys.dm_exec_describe_first_result_set(
   N'SELECT (SELECT CAST(''<o>O</o>'' AS XML) AS x) AS wrapped',0)
UNION ALL
SELECT 'Text value',system_type_name
FROM sys.dm_exec_describe_first_result_set(
N'SELECT CAST(''aaa'' AS NTEXT) AS text_string',0)
UNION ALL
SELECT 'Wrapped Text Value',system_type_name
FROM sys.dm_exec_describe_first_result_set(
  N'SELECT (SELECT CAST(''aaa'' AS NTEXT)) AS text_string_wrapped',0)

LiveDemo

输出：

╔═══════════════════════╦═════════════════════════════════════════╦══════════════════╗
║      Description      ║                   name                  ║ system_type_name ║
╠═══════════════════════╬═════════════════════════════════════════╬══════════════════╣
║ Simple XML            ║ XML_F52E2B61-18A1-11d1-B105-00805F49916 ║ ntext            ║
║ Wrapped with subquery ║ wrapped_subquery                        ║ nvarchar(max)    ║
║ XML column            ║ x                                       ║ xml              ║
║ Casted XML            ║ x                                       ║ xml              ║
║ Wrapped Casted XML    ║ wrapped                                 ║ xml              ║
║ Text value            ║ text_string                             ║ ntext            ║
║ Wrapped Text Value    ║ text_string_wrapped                     ║ ntext            ║
╚═══════════════════════╩═════════════════════════════════════════╩══════════════════╝

和：

SELECT col        -- SSMS result grid - XML column
FROM #tab
FOR XML AUTO

SELECT(SELECT col  -- text column
       FROM #tab
       FOR XML AUTO) AS wrapped_subquery

问题：

>为什么FOR XML AUTO不返回XML / NVARCHAR(MAX)数据类型,但是ntext(已弃用的数据类型！)？
>如何使用子查询来打包从ntext到nvarchar(max)的数据类型？
>为什么同样的规则不适用于XML / NTEXT列？

我知道我的问题可能是技术和内部操作,但我将不胜感激
MSDN / Connect中的任何洞察或文档？

编辑：

有趣的是当我使用普通表(不是临时的)它返回所有ntext：

╔════════════════════════╦═══════════════════════════════════════╦══════════════════╗
║      description       ║                   name                ║ system_type_name ║
╠════════════════════════╬═══════════════════════════════════════╬══════════════════╣
║ Simple XML             ║ XML_F52E2B61-18A1-11d1-B105-00805F499 ║ ntext            ║
║ Wrapped with subquery  ║ wrapped_subquery                      ║ ntext            ║
║ XML column             ║ x                                     ║ ntext            ║
║ Casted XML             ║ x                                     ║ ntext            ║
║ Wrapped Casted XML     ║ wrapped                               ║ ntext            ║
║ Text value             ║ text_string                           ║ ntext            ║
║ Wrapped Text Value     ║ text_string_wrapped                   ║ ntext            ║
╚════════════════════════╩═══════════════════════════════════════╩══════════════════╝

SqlFiddleDemo

根据TYPE directive：

sql Server support for the xml (Transact-sql) enables you to optionally
request that the result of a FOR XML query be returned as xml data type by specifying the TYPE directive.

sql Server returns XML data type instance data to the client as a
result of different server-constructs such as FOR XML queries that use
the TYPE directive,or where the xml data type is used to return XML
instance data values from sql table columns and output parameters. In
client application code,the ADO.NET provider requests this XML data
type information to be sent in a binary encoding from the server.
However,if you are using FOR XML without the TYPE directive,the XML
data comes back as a string type.

和：

SELECT 'Simple XML' AS description,system_type_name
FROM sys.dm_exec_describe_first_result_set(
   N'SELECT col AS col
     FROM #tab
     FOR XML AUTO,TYPE',system_type_name
FROM sys.dm_exec_describe_first_result_set(
   N'SELECT(SELECT col
            FROM #tab
            FOR XML AUTO,TYPE) AS wrapped_subquery',0);

LiveDemo

>为什么ntext不是nvarchar(max),因为引用XML数据作为字符串类型返回,在哪里是正常/临时表的差异？