Check if a value exists in a collection stored in XML data type column

Question

I have an XML data type column called "tags".

In that, I am storing a collection, like so:

<ArrayOfString>
    <string>personal</string>
    <string>travel</string>
    <string>gadgets</string>
    <string>parenting</string>
</ArrayOfString>

I want to select all the rows, that have one of the values that I am looking for: for example, I want to select all rows in the table that have a tag "travel".

I know that this works, if I know the index of the value I am looking for:

select * from posts
where tags.value('(/ArrayOfString/string)[1]', 'nvarchar(1000)') = 'travel'

but this query works only if the tag "travel" is the 2nd item in the nodes. How do I check if a value exists, irrespective of the position it is in?

Answer 1

select *
from tags
where tags.exist('/ArrayOfString/string[. = "travel"]') = 1

Or like this if you want to check against a variable.

declare @Val varchar(10)
set @Val = 'travel'

select *
from tags
where tags.exist('/ArrayOfString/string[. = sql:variable("@Val")]') = 1

Answer 2

You can try something like this:

SELECT
    *
FROM
    dbo.Posts
WHERE
    tags.exist('/ArrayOfString/string/text()[. = "travel"]') = 1

This will list all the rows that have "travel" in one of the strings in your XML