LIKE在SQL Server XML数据类型中的比较

I have a DB table with an XML datatype column. The column contains values like this:

我有一个带有XML数据类型列的数据库表。该列包含如下值:

<NameValuePairCollection>
  <NameValuePair Name="Foo" Value="One" />
  <NameValuePair Name="Bar" Value="Two" />
  <NameValuePair Name="Baz" Value="Three" />
</NameValuePairCollection>

I am trying to query for all rows where the XML datatype column has a value of "One" for "Foo". I am having trouble creating the appropriate XQuery to filter the results.

我试图查询XML数据类型列的值为“一”的所有行“Foo”。我无法创建适当的XQuery来过滤结果。

I found a couple related SO questions which helped me in my attempt but I still can't get it to work.

我找到了一些相关的SO问题,这些问题帮助了我,但我还是无法让它工作。

How can I query a value in SQL Server XML column
Use a LIKE statment on SQL Server XML Datatype

如何查询SQL Server XML列中的值在SQL Server XML数据类型上使用LIKE语句

Here is the SQL I have so far:

这是我到目前为止的SQL:

select * 
from MyTable 
where [XmlDataColumn].value('((/NameValuePairCollection/NameValuePair)[@Name="Foo"])[@Value]', 'varchar(max)') = 'One'
order by ID desc

Here is the error I am getting right now:

这是我现在得到的错误:

XQuery [MyTable.XmlDataColumn.value()]: Cannot atomize/apply data() on expression that contains type 'NameValuePair' within inferred type 'element(NameValuePair,#anonymous) *'

UPDATE (in response to @LeoWörteler's suggestion)

更新(回应@LeoWörteler的建议)

Based on your answer, I changed my SQL to this:

根据您的回答,我将SQL更改为:

select * 
from MyTable with(nolock) 
where [XmlDataColumn].value('/NameValuePairCollection/NameValuePair[@Name="Subject"]/@Value', 'varchar(max)') = 'One'
order by ID desc

This still doesn't work though. I get the following error:

但这仍然无效。我收到以下错误:

XQuery [MyTable.XmlDataColumn.value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xs:string *'

2 个解决方案

#1

If you want to select the value of the Value attribute instead of the whole NameValuePair element with the XPath expression in [XmlDataColumn].value(...), this should work:

如果要在[XmlDataColumn] .value(...)中使用XPath表达式选择Value属性的值而不是整个NameValuePair元素,则应该可以:

/NameValuePairCollection/NameValuePair[@Name="Foo"]/@Value

Your expression only checks if the NameValuePair has an attribute Value.

您的表达式仅检查NameValuePair是否具有属性Value。

If there are multiple elements with the correct name and you want to check if any of them has the value "One", you can use the exist(...) method:

如果有多个元素具有正确的名称,并且您想要检查它们中的任何一个是否具有值“One”,则可以使用exists(...)方法:

where [XmlDataColumn].exist(
  '/NameValuePairCollection/NameValuePair[@Name="Subject" and @Value="One"]') = 1

#2

Another option is to cast the XML as varchar, and then search for the given string as if the XML vas a varchar field.

另一种选择是将XML转换为varchar,然后搜索给定的字符串,就像XML输入varchar字段一样。

SELECT * 
FROM Table
WHERE CAST(Column as nvarchar(max)) LIKE '%Name="Foo" Value="One"%'

I love this solution as it is clean, easy to remember, hard to mess up, and can be used as a part of a where clause.

我喜欢这个解决方案因为它干净,易于记忆,难以搞砸,并且可以用作where子句的一部分。

It's not as dynamic and complete as the answer provided by Leo, but depending on the circumstances it can be a "quick n' dirty" way to get the data needed.

它不像Leo提供的答案那样动态和完整,但根据具体情况,它可以是一种“快速”的方式来获取所需的数据。

#1