Where clause on a SQL Server XML column filtering on attribute AND value

sql, sql-server, t-sql, xml

Solution

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

Problem

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?

Original source