XPath fn:data in sql server causes Type conversion in expression may affect "CardinalityEstimate" in query plan choice

Question

I have an xml variable that contains a set of ids that I want to lookup in a table. When querying I tried several version but the following (from my testing) appears to be the fastest:

declare @idsxml as xml (IdSchemaColelction) = '<root><Id>505766</Id><Id>458073</Id><Id>460689</Id><Id>464050</Id></root>'

SELECT * FROM entity
WHERE @idsXml.exist('/root/Id[data(.)=sql:column("id")]') = 1

The problem is that the query plan has the following warning "Type conversion in expression (CONVERT_IMPLICIT(sql_variant,CONVERT_IMPLICIT(numeric(38,10),[xmlTest].[dbo].[entity].[id],0),0)) may affect "CardinalityEstimate" in query plan choice"

I have created an xml schema that defines the text of Id as being an integer, so I would expect that data(.)=sql:column("id") is a comparison between integers but this warning suggests otherwise.

What is the correct way to remove this warning in this case? Does this have performance implications down the line ?

Table definition and schema definition:

CREATE XML SCHEMA COLLECTION IdSchemaColelction AS  '
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" 
  xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" >
  <xs:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" 
             schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd"/>
  <xs:element name="root">
    <xs:complexType>
      <xs:sequence>
        <xs:element maxOccurs="unbounded" name="Id" type="sqltypes:int" />
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>
'
go

create table entity ( id int not null  primary key)

Answer 1

I do not think, that your approach is the best or fastest...

Here are some approaches to compare them:

Use this for tests

create table test ( id int not null  primary key);
insert into test VALUES(100),(200),(505766),(300),(400),(500),(458073),(600),(700),(464050),(800),(900),(1000)
GO

Here is your list of ids

declare @idsxml as xml = '<root><Id>505766</Id><Id>458073</Id><Id>460689</Id><Id>464050</Id></root>'

--This is your approach. It will parse the XML over and over --.data() is - for sure - not the best way to read type safe data for comparison...

SELECT test.id
FROM test
WHERE @idsXml.exist('/root/Id[data(.)=sql:column("id")]') = 1;

--This is exactly the same approach but with a faster XQuery

SELECT test.id
FROM test
WHERE @idsXml.exist('/root/Id[text()=sql:column("id")]') = 1;

--This is a bit slower... Probably because there's an implicit type conversion...

SELECT test.id
FROM test
WHERE @idsXml.exist('/root[Id=sql:column("id")]') = 1;

--With a larger list it might be better to use a derived table in an INNER JOIN

WITH DerivedTable AS
(
    SELECT i.value('.','int') AS id
    FROM @idsxml.nodes('root/Id') AS A(i)
)
SELECT test.id
FROM test 
INNER JOIN DerivedTable AS dt ON test.id=dt.id;

--And with a big list you might even think about an indexed declared table (read about in memory for the last quantum speed)

DECLARE @tbl TABLE(id INT NOT NULL PRIMARY KEY) --PK only, if your XML never contains a value twice!
INSERT INTO @tbl
SELECT i.value('.','int') AS id
FROM @idsxml.nodes('root/Id') AS A(i);

SELECT test.id
FROM test 
INNER JOIN @tbl AS tbl ON test.id=tbl.id;

GO

DROP TABLE test;