4

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

4

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
)
|improve this answer|||||
4

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
)
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.