sql – SELECT FOR XML AUTO并返回数据类型

发布时间：2020-12-12 08:40:56 所属栏目：MsSql教程来源：网络整理

导读：在播放sys.dm_exec_describe_first_result_set时,我得到了这样的一点： CREATE TABLE #tab(col INT,x XML );INSERT INTO #tab(col,x) VALUES (1,NULL),(2,(3,'ax/a');SELECT 'Simple XML' AS description,name,system_type_nameFROM sys.dm_exec_describe_fir

在播放sys.dm_exec_describe_first_result_set时,我得到了这样的一点：

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数据作为字符串类型返回,在哪里是正常/临时表的差异？

解决方法

FOR XML在SQL Server 2000中引入.

SQL Server 2000没有MAX数据类型或XML数据类型.在子查询中也不可能使用FOR XML.

文章What does server side FOR XML return?解释

In SQL Server 2000 … FOR XML… was
implemented in the layer of code between the query processor and the
data transport layer … the query processor produces the result the same way as without
FOR XML and then FOR XML code formats the rowset as XML. For maximum
XML publishing performance FOR XML does steaming XML formatting of the
resulting rowset and directly sends its output to the server side TDS
code in small chunks without buffering whole XML in the server space.
The chunk size is 2033 UCS-2 characters. Thus,XML larger than 2033
UCS-2 characters is sent to the client side in multiple rows each
containing a chunk of the XML. SQL Server uses a predefined column
name for this rowset with one column of type NTEXT –
“XML_F52E2B61-18A1-11d1-B105-00805F49916B” – to indicate chunked XML
rowset in UTF-16 encoding.

所以看来,这仍然是与后期版本的顶级FOR XML相同的方法.

SQL Server 2005引入了在子查询中使用FOR XML的能力(意味着这些现在需要由查询处理器处理,而不是外部的层,同时将结果流式传输到客户端)

同一篇文章解释说,这些将被输入为NVARCHAR(MAX)或XML,这取决于类型指令的存在与否.

除了数据类型的区别,这意味着如果#tab很大,附加的SELECT包装器可以在性能上产生巨大的差异.

/*Can be streamed straight out to client without using server storage*/
SELECT col
FROM #tab
FOR XML AUTO

/*XML constructed in its entirety in tempdb first*/
SELECT(SELECT col
FROM #tab
FOR XML AUTO) AS wrapped_subquery

可以看到调用堆栈中的不同方法以及执行计划.

直接流

sqllang.dll!CXMLExecContext::AddTagAndAttributes()  + 0x5a9 bytes                   
sqllang.dll!CXMLExecContext::AddXMLRow()  + 0x2b7 bytes                 
sqltses.dll!CEsExec::FastMoveEval()  + 0x9c bytes                   
sqllang.dll!CXStmtQuery::ErsqExecuteQuery()  + 0x280 bytes                  
sqllang.dll!CXStmtXMLSelect::WrapExecute()  + 0x2d7 bytes                   
sqllang.dll!CXStmtXMLSelect::XretDoExecute()  + 0x355 bytes                 
sqllang.dll!CXStmtXMLSelect::XretExecute()  + 0x46 bytes                    
sqllang.dll!CMsqlExecContext::ExecuteStmts<1,1>()  + 0x368 bytes                    
sqllang.dll!CMsqlExecContext::FExecute()  + 0x6cb bytes                 
sqllang.dll!CSQLSource::Execute()  + 0x3ee bytes                    
sqllang.dll!process_request()  + 0x757 bytes

带子查询

sqllang.dll!CXMLExecContext::AddTagAndAttributes()  + 0x5a9 bytes
sqllang.dll!CXMLExecContext::AddXMLRow()  + 0x2b7 bytes
sqllang.dll!CForXmlSerialize::ProcessRow()  + 0x19 bytes
sqllang.dll!CUDXR_Base::PushRow()  + 0x30 bytes
sqlmin.dll!CQScanUdx::Open()  + 0xd5 bytes
sqlmin.dll!CQueryScan::StartupQuery()  + 0x170 bytes
sqllang.dll!CXStmtQuery::SetupQueryScanAndExpression()  + 0x391 bytes
sqllang.dll!CXStmtQuery::InitForExecute()  + 0x34 bytes
sqllang.dll!CXStmtQuery::ErsqExecuteQuery()  + 0x217 bytes
sqllang.dll!CXStmtSelect::XretExecute()  + 0xed bytes
sqllang.dll!CMsqlExecContext::ExecuteStmts<1,1>()  + 0x368 bytes
sqllang.dll!CMsqlExecContext::FExecute()  + 0x6cb bytes
sqllang.dll!CSQLSource::Execute()  + 0x3ee bytes
sqllang.dll!process_request()  + 0x757 bytes

两者最终都调用相同的底层XML代码,但是“展开的”版本在计划本身中没有任何XML迭代器,结果是通过用CXStmtXMLSelect替换来自CXStmtSelect的方法调用来实现的(在计划中作为XML选择根节点,而不是普通的老选择).

在SQL Server 2016 CTP3上,我仍然看到顶级FOR XML的ntext.不过顶级FOR JSON显示为nvarchar(max)

至少在CTP中,JSON特殊列名仍然包含GUID F52E2B61-18A1-11d1-B105-00805F49916B,尽管其原因是the IXMLDocument Interface.

尽管XML Select被替换为JSON Select,计划看起来是一样的

BTW：构建Microsoft SQL Server 2014 – 12.0.4213.0(X64)我没有看到临时表和永久表之间的行为有任何差异.这可能是您的问题使用的环境http://sqlfiddle.com/(12.0.2000.8)和https://data.stackexchange.com/(12.0.4213.0)之间的不同@@版本.

也许在两个2014版本之间的sys.dm_exec_describe_first_result_set中修复了一个错误.

在2012年,我得到的结果与Shnugo在11.0.5343.0(前三行为NULL)相同,但在安装SP3 11.0.6020.0之后,我得到与您的初始结果相同的问题.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!