Where clause on a SQL Server XML column filtering

问题:

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?

回答1:

There is no need to extract the value. Use exist() Method (xml Data Type) instead.

select *
from Document
where Metadata.exist('/properties/property[@name="reportId" and . = 55]') = 1

回答2:

Nevermind, got it. For reference:

select * from Document
where Metadata.value('(/properties/property[@name="reportId"])[1]', 'int') = 55