XPath fn: data in SQL server causes type conversion in an expression to affect CardinalityEstimate in query plan selection

Question

I have an xml variable containing a set of identifiers that I want to look up in a table. Upon request, I tried several versions, but the following (from my testing) turned out 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: "Converting a type to an expression (CONVERT_IMPLICIT (sql_variant, CONVERT_IMPLICIT (numeric (38,10), [xmlTest]. [Dbo]. [Entity]. [Id], 0) , 0)) may affect "CardinalityEstimate" in the query plan selection "

I created an xml schema that defines the text Id as an integer, so I would expect it to data(.)=sql:column("id")be a comparison of integers, but this warning points to something else.

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

Definition and definition of the table:

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 your approach is the best or fast ...

Here are some ways 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 identifiers

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

- . XML - .data() - - ...

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

- , XQuery

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

- ... , ...

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

- 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;

- ( in memory )

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;