Using XML.Query for filtering multiple values

Question

Hi

I have XML column in my table which can contain XML like these 2 records :

record 1, suppose that ID of record is 1 and XML content is

<content>
 <node1 >10</node1>
 <node2 >20</node2>
 <node3 >30</node3>
 <node4 >40</node4>
 <node5 >50</node5> 
</content>

record 2 , suppose that ID of record is 2 and XML content is

<content>
 <node_name_1 >10</node_name_1>
 <hello >20</hello>
</content>

I want to know IDs of records with inner node value of 10 .

declare @ids nvarchar(500)
set @ids = '10';
select id from tablename
 where
 convert(nvarchar(max),xmlfieldname.query('data(/content/* = ( sql:variable("@ids") )  )') ) = 'true'

this is working perfectly , but my problem is when I want to use multiple values for inner nodes . I want to know IDs of records which inner values are either 10 or 20 . This query works perfect :

 select id from tablename
 where
 convert(nvarchar(max),xmlfieldname.query('data(/content/* = ( 10,20 )  )') ) = 'true'

But when I use sql:variable , nothing returned !

declare @ids nvarchar(500)
set @ids = '10,20';
select id from tablename
 where
 convert(nvarchar(max),xmlfieldname.query('data(/content/* = ( sql:variable("@ids") )  )') ) = 'true'

how can I use something like Select * from mytable where fieldname in ('123','456') in XML ?

Answer 1

You're asking for it to find the entry which has "10,20" for that value, as if you were saying "where fieldname in ('10,20')".

It's the age-old Split question...

So... try:

declare @qry nvarchar(max);
select @qry = 'select id from tablename where convert(nvarchar(max),xmlfieldname.query(''data(/content/* = ( ' + @ids + ' )  )'') ) = ''true''';
exec sp_executesql @qry

Rob