Check if the value exists in the collection stored in the column of the XML data type.

Question

I have an XML type column called "tags".

In this I store a collection, for example:

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

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

I know this works if I know the index of the value I'm looking for:

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

but this query only works if the "travel" tag is the second element in the nodes. How to check if any value exists regardless of its position?

Answer 1

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

Or so if you want to check 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 

Listed here are all the lines that have a "move" in one of the lines in XML