SQL Server XML Type Select Where Attribute = X From Any Tag

select *
from tablename
where CONVERT(xml, Sections).value('(/sections/section/@value)[1]', 'varchar(1)') = 'f'

will properly retrieve a record with the following value in the Sections column:

<sections><section value="f" priority="4" /><section value="a" priority="4" /></sections>

But misses this:

<sections><section value="w" priority="4" /><section value="f" priority="4" /></sectio开发者_开发百科ns>

Obviously this is the problem "/sections/section/@value)[1]" but I don't understand the syntax and Google hasn't been too helpful. I found some code that got me this far, but I don't know how to modify it so that it will look through all tags instead of just the first one. I tried dropping the [1] but that gave the following error:

XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

You can use exist().

select *
from tablename
where CONVERT(xml, Sections).exist('/sections/section[@value = "f"]') = 1

If you want to use some dynamic value instead a hard coded f in the query you can use sql:variable().

declare @Value varchar(10) = 'f'

select *
from tablename
where CONVERT(xml, Sections).exist('/sections/section[@value = sql:variable("@Value")]') = 1

If you have multiple entries of an XML tag, you need to use the .nodes() XQuery method:

select 
    *,
    Sections(Section).value('(@value)[1]', 'varchar(1)') 
from tablename
cross apply CONVERT(xml, Sections).nodes('/sections/section') AS Sections(Section)

With this, you create a "pseudo-table" called Sections(Section) that contains one XML row for each element that matches your XPath (for each <section> under <sections>). You can then reach into this pseudo-table and extract individual bits of information from those XML "rows" using hte usual .value() method