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) 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:
- Why
FOR XML AUTOdoesn't returnXML/NVARCHAR(MAX)datatype butntext(deprecated datatype!)? - How wrapping with subquery changes datatype from
ntexttonvarchar(max)? - Why the same rules don't apply to
XML/NTEXTcolumns?
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