Specifying SQL variable to xquery exist method to check if value is in given set of values

Question

I am trying to query an XML column to return all rows where an attribute is in a list of possible values.

XQuery allows something like

SELECT COUNT(*) 
FROM table
WHERE xml_col.exist('//Field.[@name=("value1","value2","value3")]') = 1

which would return the number of records that have a Field with attribute @name set to either "value1", "value2" or "value3".

What I'd like to do is write a concise query that could handle the set "value1", "value2", "value3" as an input parameter, e.g.

DECLARE @list NVARCHAR(100)

SET @list = '("value1","value2","value3")'

SELECT COUNT(*) 
FROM table
WHERE xml_col.exist('//Field.[@name=sql:variable("@list")]') = 1

which, of course, is invalid. Any suggestions would be appreciated!

Answer 1

simplest way to do it is (if your name could not contain ,):

declare @list nvarchar(max) = ',value1,value2,value3,'

select count(*) 
from test
where xml_col.exist('//Field[contains(sql:variable("@list"), concat(",", @name, ","))]') = 1;

or SQL way:

select count(*) 
from test
where
    exists
    (
         select 1 from xml_col.nodes('//Field') as T(C)
         where T.C.value('@name', 'nvarchar(max)') in ('value1', 'value2', 'value3')
    )

sql fiddle demo