gotchaMinor
Why does assigning FOR XML to an XML variable warn about CONVERT_IMPLICIT(XML)?
Viewed 0 times
whyxmlconvert_implicitfordoesaboutwarnvariableassigning
Problem
SET STATISTICS XML ON;
DECLARE @x XML = (SELECT 'hi' "@id" FOR XML PATH ('this'), root ('xml'));Type conversion in expression (CONVERT_IMPLICIT(xml,[Expr1001],0)) may affect "CardinalityEstimate" in query plan choice
Why is a conversion happening? I thought that
FOR XML caused the result to be the XML type already. If this warning can/should be ignored, how do I stop the warning from appearing in the execution plan (so that when I debug a bigger query with a similar warning it doesn’t distract me from actual issues)?Solution
The return type of the
I was being confused by the fact that SSMS renders any column named
I was able to figure out the actual type by getting SQL Server to spit out a type-conversion error:
To fix the warning completely, simply add the
FOR XML PATH expression is NVARCHAR(MAX), not XML.I was being confused by the fact that SSMS renders any column named
XML_F52E2B61-18A1-11d1-B105-00805F49916B as if it has the XML data type. If I run the following command, I get a clickable XML result cell in SSMS:SELECT 'hi' "@id" FOR XML PATH ('this'), root ('xml');I was able to figure out the actual type by getting SQL Server to spit out a type-conversion error:
SELECT SQL_VARIANT_PROPERTY((SELECT 'hi' "@id" FOR XML PATH ('this'), root ('xml')), 'BaseType');Msg 206, Level 16, State 2, Line 1
Operand type clash: nvarchar(max) is incompatible with sql_variantTo fix the warning completely, simply add the
TYPE directive to the FOR XML clause:SET STATISTICS XML ON;
DECLARE @x XML = (SELECT 'hi' "@id" FOR XML PATH ('this'), root ('xml'), TYPE);Code Snippets
SELECT 'hi' "@id" FOR XML PATH ('this'), root ('xml');SELECT SQL_VARIANT_PROPERTY((SELECT 'hi' "@id" FOR XML PATH ('this'), root ('xml')), 'BaseType');Msg 206, Level 16, State 2, Line 1
Operand type clash: nvarchar(max) is incompatible with sql_variantSET STATISTICS XML ON;
DECLARE @x XML = (SELECT 'hi' "@id" FOR XML PATH ('this'), root ('xml'), TYPE);Context
StackExchange Database Administrators Q#114511, answer score: 5
Revisions (0)
No revisions yet.