检查存储在XML数据类型列中的集合中是否存在值

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

我有一个名为“tags”的XML数据类型列。

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".

我想选择所有具有我正在寻找的值的行：例如，我想选择表中具有“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?

但此查询仅在标记“travel”是节点中的第2项时有效。如何检查值是否存在，无论其位置如何？

2 个解决方案

#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

#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

这将列出XML中某个字符串中“travel”的所有行

#1

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