CODEWITHSUNDEEP
sql-serverxml
cool breeze
cool breeze

Reputation: 4811

Where IN clause when column is a XML data type

<users>
    <user key="location" value="123" />
</users>

How can I do a WHERE IN (123,456,789) type query when the value is a XML column?

My query so far is below:

select *
from users
where meta.exist('//user[./@key="location" and ./@value="123"]') = 1

Upvotes: 0

Views: 97

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67321

As Alan's solutions shows you, one way is to read the whole set and use an external WHERE to filter this. This works and can be the best approach. But with large XMLs .exist() is much faster, because the shredding is stopped after the first hit.

Alternatively you can try this:

Credits to Alan Burstein for the MCVE

-- Sample data
DECLARE @users TABLE (userId INT IDENTITY, meta XML)
INSERT @users(meta) VALUES 
('<users><user key="location" value="000" /></users>'),
('<users><user key="location" value="123" /></users>'),
('<users><user key="location" value="456" /></users>'),
('<users><user key="location" value="789" /></users>'),
('<users><user key="location" value="999" /></users>');

--I frame the number list with commas

DECLARE @SearchFor VARCHAR(100)=',123,456,789,';

--A simple contains() looking for ,123, will find the string

SELECT *
FROM @users
WHERE meta.exist('/users/user[@key="location" and contains(sql:variable("@SearchFor"),concat(",",@value,","))]')=1;

We can use sql:variable() to introduce an external variable into the XQuery. (You can do the same with the "location" of course...)

If your list of numbers is taken from a table, you might try something along this:

DECLARE @SearchFor TABLE(LookUpNumber INT);
INSERT INTO @SearchFor VALUES(123),(456),(789);

SELECT *
FROM @users
CROSS APPLY(SELECT CONCAT((SELECT CONCAT(',',LookUpNumber) FROM @SearchFor FOR XML PATH('')),','))  A(SearchFor)
WHERE meta.exist('/users/user[@key="location" and contains(sql:column("SearchFor"),concat(",",@value,","))]')=1;

The APPLY will create the concatenated list of values on-the-fly and pass it into the XML using sql:column().

Upvotes: 2

Alan Burstein
Alan Burstein

Reputation: 7928

There's many ways to do this. Here's the most straightforward. 

-- Sample data
DECLARE @users TABLE (userId INT IDENTITY, meta XML)
INSERT @users(meta) VALUES 
('<users><user key="location" value="000" /></users>'),
('<users><user key="location" value="123" /></users>'),
('<users><user key="location" value="456" /></users>'),
('<users><user key="location" value="789" /></users>'),
('<users><user key="location" value="999" /></users>');

-- Solution
SELECT      u.userId, uv.val
FROM        @users AS u
CROSS APPLY (
            VALUES(meta.value('(//user/@value)[1]', 'int'),
                meta.value('(//user/@key)[1]', 'VARCHAR(8)'))
        ) AS uv(val,[key])
 WHERE  uv.val IN (123,456,789) and uv.[key]='location'

Returns:

userId      val
----------- -----------
2           123
3           456
4           789

Upvotes: 3

Related Questions