XML : SELECT FOR XML AUTO and return datatypes

During playing with sys.dm_exec_describe_first_result_set I get to this point:

 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

Output:

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

And:

 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  

Questions:

  1. Why FOR XML AUTO doesn't return XML/NVARCHAR(MAX) datatype but ntext (deprecated datatype!)?
  2. How wrapping with subquery changes datatype from ntext to nvarchar(max)?
  3. Why the same rules don't apply to XML/NTEXT columns?

I know my questions may be technical and internal operations, but I would be grateful for any insight or documentation in MSDN/Connect?

No comments:

Post a Comment