Why does assigning FOR XML to an XML variable warn about CONVERT_IMPLICIT(XML)?

Question

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)?

Answer 1

The return type of 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_variant

To 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);