如何在SQL中查询XML字段的特定节点？

The issue with your query is that you’re treating elem as a child element instead of an attribute of the prop node. In your XML, elem="_NoFilter" is an attribute, not a nested element—so you need to use the @ prefix in your XPath to target attributes correctly.

Additionally, since the _NoFilter prop has a specific namespace identifier (ns="ARM:"), it’s a good idea to include that in your predicate to ensure you’re matching the exact node you want (in case there are other props with the same elem value but different ns values).

Corrected Query to Retrieve the Full Prop Node #

;WITH XMLNAMESPACES(DEFAULT 'http://www.xyz.de/activerepository/fileprops')
SELECT 
    CAST(properties AS XML).query('root/props/prop[@ns="ARM:" and @elem="_NoFilter"]'),
    *
FROM tm_cas_files (NOLOCK)
WHERE id = 'A7ED7D99-8CDB-47F6-8EF5-18E7FCB97F28'

If You Only Need the Value Inside the Node #

If you don’t need the entire prop node and just want the actual value (like the boolean true in this case), use the value() method instead of query():

;WITH XMLNAMESPACES(DEFAULT 'http://www.xyz.de/activerepository/fileprops')
SELECT 
    CAST(properties AS XML).value('(root/props/prop[@ns="ARM:" and @elem="_NoFilter"]/value)[1]', 'BIT') AS NoFilterValue,
    *
FROM tm_cas_files (NOLOCK)
WHERE id = 'A7ED7D99-8CDB-47F6-8EF5-18E7FCB97F28'

Why Your Original Query Returned Empty Results #

Your XPath root/props/prop[elem="_NoFilter"] was looking for prop nodes that have a child element named elem with the value "_NoFilter". But in your XML, elem is an attribute, not a child element—so that predicate never matched any nodes, resulting in an empty output.

内容的提问来源于stack exchange，提问作者dns_nx