patternsqlMinor
Execution Plan Warnings cardinality xml data type
Viewed 0 times
cardinalitywarningsxmltypeplandataexecution
Problem
I have the following statements:
-
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
-- 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 stringDECLARE @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.