Execution Plan Warnings cardinality xml data type

Question

I have the following statements:

-- 1st
DECLARE @AuditParameters XML = (
    SELECT
        1 AS AccountID,
        2 AS CategoryID,
        3 AS CategoryAttributeID,
        '4' AS SyncBatchGUID
    FOR XML PATH(N'Parameters'), ELEMENTS XSINIL
)

-

-- 2nd
DECLARE @AuditParameters XML = convert(xml, (
    SELECT
        1 AS AccountID,
        2 AS CategoryID,
        3 AS CategoryAttributeID,
        '4' AS SyncBatchGUID
    FOR XML PATH(N'Parameters'), ELEMENTS XSINIL
))

Both statements result in the same execution plan, however, they display the warning message

Type conversion in expression (CONVERT_IMPLICIT(xml,[Expr1004],0)) may affect "CardinalityEstimate" in query plan choice.

You may note I have tried to convert the second statement to XML and yet I get the same warning message.

How can I get rid of this warning message on the statement above?

Thank you

score 4 · Answer 1 · answered Feb 19 '20 at 03:48

Add TYPE to return XML from the inner query rather than a string

DECLARE @AuditParameters XML = (
    SELECT
        1 AS AccountID,
        2 AS CategoryID,
        3 AS CategoryAttributeID,
        '4' AS SyncBatchGUID
    FOR XML PATH(N'Parameters'), ELEMENTS XSINIL, TYPE
)

score 4 · Accepted Answer · edited Feb 19 '20 at 03:57

Use TYPE Directive in FOR XML Queries

SQL Server support for the xml (Transact-SQL) enables you to optionally request that the result of a FOR XML query be returned as xml data type by specifying the TYPE directive.

-- 1st
DECLARE @AuditParameters XML = (
    SELECT
        1 AS AccountID,
        2 AS CategoryID,
        3 AS CategoryAttributeID,
        '4' AS SyncBatchGUID
    FOR XML PATH(N'Parameters'), ELEMENTS XSINIL,Type
)

Execution Plan Warnings cardinality xml data type

2 Answers2