CODEWITHSUNDEEP
sql-server-2008xquerysqlxml
gice
gice

Reputation: 95

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

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!

Upvotes: 2

Views: 2364

Answers (3)

roman
roman

Reputation: 117337

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

Upvotes: 3

i-one
i-one

Reputation: 5120

You may try following construct:

select count(1)
from [table] t
where exists (
    select 1 from (values ('value1'),('value2'),('value3')) l(v)
    where t.xml_col.exist('//Field[@name=sql:column("l.v")]') = 1);

Also it can be used with table variable or table valued parameter in the following way:

declare @list table (value varchar(100))
insert into @list values ('value1'),('value2'),('value3')

or

create type ListOfValues as table (value varchar(100))
GO
declare @list ListOfValues
insert into @list values ('value1'),('value2'),('value3')

and then

select count(1)
from [table] t
where exists(select 1 from @list l
    where t.xml_col.exist('//Field[@name=sql:column("l.value")]') = 1);

Upvotes: 1

FrankPl
FrankPl

Reputation: 13315

Maybe in this case it would be easier to check on the SQL side:

SELECT COUNT(*) 
FROM table
WHERE xml_col.value('(//Field/@name)[1]', 'nvarchar(255)') in ('value1', 'value2', 'value3')

At least this would work if there is only one Field element in your XML, otherwise it would get a bit more complex.

Upvotes: 1

Related Questions