HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Execution Plan Warnings cardinality xml data type

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
cardinalitywarningsxmltypeplandataexecution

Problem

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

Solution

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
)

Code Snippets

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

Context

StackExchange Database Administrators Q#259972, answer score: 4

Revisions (0)

No revisions yet.