SQL Server XML 列上的 Where 子句过滤属性 AND 值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13964682/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Where clause on a SQL Server XML column filtering on attribute AND value
提问by user1380769
I have a 'Metadata' field in table 'Document' that has the following data:
我在表“文档”中有一个“元数据”字段，其中包含以下数据：
<properties>
<property name="someProp">xyz</property>
<property name="reportId">55</property>
<property name="someOtherProp">abc</property>
</properties>'
How can I write a query that returns records where a property element exists with a name of "reportId" and that "reportId" property element has a value of 55? Sometimes the "reportId" property node is the only one that exists, sometimes not, and it's not always in the above order so I can't query on absolute positions. Any ideas?
如何编写一个查询来返回存在名称为“reportId”的属性元素并且该“reportId”属性元素的值为 55 的记录？有时“reportId”属性节点是唯一存在的节点，有时不存在，并且它并不总是按上述顺序排列，因此我无法查询绝对位置。有任何想法吗？
回答by Mikael Eriksson
There is no need to extract the value. Use exist() Method (xml Data Type)instead.
无需提取值。改用exist() 方法（xml 数据类型）。
select *
from Document
where Metadata.exist('/properties/property[@name="reportId" and . = 55]') = 1
回答by user1380769
Nevermind, got it. For reference:
没关系，明白了。以供参考：
select * from Document
where Metadata.value('(/properties/property[@name="reportId"])[1]', 'int') = 55
