sql - tabla - SELECCIONAR PARA XML AUTO y devolver tipos de datos

la cláusula for xml no está permitida en una instrucción insert

Question

Durante la reproducción con sys.dm_exec_describe_first_result_set llego a este punto:

CREATE TABLE #tab(col INT, x XML );
INSERT INTO #tab(col,x) VALUES (1,NULL), (2,NULL), (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'', name, system_type_name
FROM sys.dm_exec_describe_first_result_set(
   N''SELECT(SELECT col
            FROM #tab
            FOR XML AUTO) AS wrapped_subquery'', NULL, 0)
UNION ALL 
SELECT ''XML column'', name, system_type_name
FROM sys.dm_exec_describe_first_result_set(
   N''SELECT x FROM #tab '', NULL, 0)
UNION ALL
SELECT ''Casted XML'', name, system_type_name
FROM sys.dm_exec_describe_first_result_set(
   N''SELECT CAST(''''<o>O</o>'''' AS XML) AS x'', NULL, 0)
UNION ALL
SELECT ''Wrapped Casted XML'', name, system_type_name
FROM sys.dm_exec_describe_first_result_set(
   N''SELECT (SELECT CAST(''''<o>O</o>'''' AS XML) AS x) AS wrapped'', NULL, 0)
UNION ALL
SELECT ''Text value'', name, system_type_name
FROM sys.dm_exec_describe_first_result_set(
N''SELECT CAST(''''aaa'''' AS NTEXT) AS text_string'', NULL, 0)
UNION ALL
SELECT ''Wrapped Text Value'', name, system_type_name
FROM sys.dm_exec_describe_first_result_set(
  N''SELECT (SELECT CAST(''''aaa'''' AS NTEXT)) AS text_string_wrapped'', NULL, 0)

LiveDemo

Salida:

╔═══════════════════════╦═════════════════════════════════════════╦══════════════════╗
║      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            ║
╚═══════════════════════╩═════════════════════════════════════════╩══════════════════╝

Y:

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

Preguntas:

1. ¿Por qué FOR XML AUTO no devuelve XML/NVARCHAR(MAX) tipo de datos pero ntext (deprecated datatype!)?
2. Cómo envolver con sub consulta cambia el tipo de datos de ntext a nvarchar(max) ?
3. ¿Por qué las mismas reglas no se aplican a las columnas XML/NTEXT ?

Sé que mis preguntas pueden ser operaciones técnicas e internas, pero agradecería cualquier información o documentación en MSDN / Connect.

EDITAR:

Lo curioso es que cuando uso la tabla normal (no temporal) devuelve todo 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

De acuerdo con la TYPE directive :

El soporte de SQL Server para el xml (Transact-SQL) le permite opcionalmente solicitar que el resultado de una consulta FOR XML se devuelva como tipo de datos xml especificando la directiva TYPE .

SQL Server devuelve datos de instancia de tipo de datos XML al cliente como resultado de diferentes construcciones de servidor, como FOR XML consultas que utilizan la directiva TYPE, o donde el tipo de datos xml se utiliza para devolver valores de datos de instancia XML desde columnas de tabla SQL y salida parámetros. En el código de la aplicación cliente, el proveedor ADO.NET solicita que esta información de tipo de datos XML se envíe en una codificación binaria del servidor. Sin embargo, si está utilizando FOR XML sin la directiva TYPE, los datos XML vuelven como un tipo de cadena .

Y:

SELECT ''Simple XML'' AS description, name, system_type_name
FROM sys.dm_exec_describe_first_result_set(
   N''SELECT col AS col
     FROM #tab
     FOR XML AUTO, TYPE'', NULL, 0)  
UNION ALL
SELECT ''Wrapped with subquery'', name, system_type_name
FROM sys.dm_exec_describe_first_result_set(
   N''SELECT(SELECT col
            FROM #tab
            FOR XML AUTO,TYPE) AS wrapped_subquery'', NULL, 0);

LiveDemo

4. ¿Por qué ntext no nvarchar(max) como en quote the XML data comes back as a string type y donde está la diferencia normal / tabla temporal?

Answer 1

¡Interesante pregunta! ¡El NTEXT es realmente extraño!

Tengo una idea acerca de la subconsulta: cuando devuelves un XML siempre se transfiere como cadena, a menos que especifiques TYPE (seguramente lo sabes de XML anidado con CROSS APPLY o de concatenación de cadenas con STUFF, donde a veces ves esto con TYPE y un .value() y algunas veces "desnudo").

Realmente no podría reproducir sus resultados (SQL Server 2012 también). Copia simple de copia regresa con (quería probar con la variable de tabla declarada y el valor de retorno de una función):

Simple XML              NULL                  NULL
Wrapped with subquery   NULL                  NULL
XML column              NULL                  NULL
Casted XML              x                     xml
Wrapped Casted XML      wrapped               xml
Text value              text_string           ntext
Wrapped Text Value      text_string_wrapped   ntext

EDITAR: Hubo una nueva observación que pensé que no estaba clara, pero fue mi error ... Me lo quité ...

Answer 2

FOR XML se introdujo en SQL Server 2000.

SQL Server 2000 no tenía tipos de datos MAX o el tipo de datos XML . Tampoco fue posible usar FOR XML en una sub consulta.

El artículo ¿Qué devuelve el servidor FOR XML? explica

En SQL Server 2000 ... FOR XML ... se implementó en la capa de código entre el procesador de consultas y la capa de transporte de datos ... el procesador de consultas produce el resultado de la misma manera que sin FOR XML y luego FOR XML formatos de código FOR XML el conjunto de filas como XML. Para obtener el máximo rendimiento de publicación XML, FOR XML procesa el formato XML del conjunto de filas resultante y envía directamente su salida al código TDS del servidor en fragmentos pequeños sin almacenar todo el XML en el espacio del servidor. El tamaño del fragmento es 2033 UCS-2 caracteres. Por lo tanto, XML de más de 2033 caracteres UCS-2 se envía al lado del cliente en varias filas, cada una con un fragmento del XML. SQL Server utiliza un nombre de columna predefinido para este conjunto de filas con una columna de tipo NTEXT - " XML_F52E2B61-18A1-11d1-B105-00805F49916B " - para indicar el conjunto de filas XML fragmentado en la codificación UTF-16.

Así que parece que esto todavía se implementa de la misma manera para FOR XML nivel superior en versiones posteriores también.

SQL Server 2005 introdujo la capacidad de utilizar FOR XML en subconsultas (lo que significa que ahora deben ser manejadas por el procesador de consultas en lugar de una capa externa mientras transmiten los resultados al cliente)

El mismo artículo explica que estos se escribirán como NVARCHAR(MAX) o XML función de la presencia o no de una directiva de type .

Además de la diferencia de tipo de datos, esto significa que el contenedor SELECT adicional puede hacer una gran diferencia en el rendimiento si #tab es grande.

/*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

Es posible ver los diferentes enfoques en las pilas de llamadas y en los planes de ejecución.

Directamente transmitido

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    

Con sub consulta

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

Ambos terminan llamando al mismo código XML subyacente, pero la versión "sin envolver" no tiene ningún iterador XML en el plan en sí, el resultado se logra reemplazando las llamadas a CXStmtSelect de CXStmtSelect con CXStmtXMLSelect en CXStmtXMLSelect lugar (representadas en el plan como raíz de selección de XML) nodo en lugar de un simple viejo Seleccionar).

En SQL Server 2016 CTP3 sigo viendo ntext para nivel superior FOR XML . Sin embargo, el nivel superior FOR JSON aparece como nvarchar(max)

Al menos en el CTP, el nombre de la columna especial JSON aún contiene el GUID F52E2B61-18A1-11d1-B105-00805F49916B pesar de que el origen de esto es la interfaz IXMLDocument .

Los planes se ven muy similares aunque el XML Select se reemplaza con un JSON Select

Por cierto: en la compilación de Microsoft SQL Server 2014 - 12.0.4213.0 (X64) no veo ninguna diferencia en el comportamiento entre las tablas temporales y las tablas permanentes. Esto probablemente se debe a la diferente @@Version entre los entornos en los que su pregunta utiliza http://sqlfiddle.com/ (12.0.2000.8) y https://data.stackexchange.com/ (12.0.4213.0).

Tal vez se corrigió un error en sys.dm_exec_describe_first_result_set entre las dos compilaciones de 2014.

En 2012 obtengo los mismos resultados que Shnugo en 11.0.5343.0 (con NULL en las primeras tres filas) pero después de instalar SP3 11.0.6020.0 recibo lo mismo que los resultados iniciales que se muestran en la pregunta.