Where clause on a SQL Server XML column filtering on attribute AND value
24,209
Solution 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
Solution 2
Nevermind, got it. For reference:
select * from Document
where Metadata.value('(/properties/property[@name="reportId"])[1]', 'int') = 55
Author by
user1380769
Updated on July 09, 2022Comments
-
user1380769 almost 2 years
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?