LIKE comparison in SQL Server XML datatype

Question

I have a DB table with an XML datatype column. The column contains values like this:

<NameValuePairCollection>
  <NameValuePair Name="Foo" Value="One" />
  <NameValuePair Name="Bar" Value="Two" />
  <NameValuePair Name="Baz" Value="Three" />
</NameValuePairCollection>

I am trying to query for all rows where the XML datatype column has a value of "One" for "Foo". I am having trouble creating the appropriate XQuery to filter the results.

I found a couple related SO questions which helped me in my attempt but I still can't get it to work.

How can I query a value in SQL Server XML column
Use a LIKE statment on SQL Server XML Datatype

Here is the SQL I have so far:

select * 
from MyTable 
where [XmlDataColumn].value('((/NameValuePairCollection/NameValuePair)[@Name="Foo"])[@Value]', 'varchar(max)') = 'One'
order by ID desc 

Here is the error I am getting right now:

XQuery [MyTable.XmlDataColumn.value()]: Cannot atomize/apply data() on expression that contains type 'NameValuePair' within inferred type 'element(NameValuePair,#anonymous) *'

UPDATE (in response to @LeoWörteler's suggestion)

Based on your answer, I changed my SQL to this:

select * 
from MyTable with(nolock) 
where [XmlDataColumn].value('/NameValuePairCollection/NameValuePair[@Name="Subject"]/@Value', 'varchar(max)') = 'One'
order by ID desc 

This still doesn't work though. I get the following error:

XQuery [MyTable.XmlDataColumn.value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xs:string *'

Answer 1

If you want to select the value of the Value attribute instead of the whole NameValuePair element with the XPath expression in [XmlDataColumn].value(...), this should work:

/NameValuePairCollection/NameValuePair[@Name="Foo"]/@Value

Your expression only checks if the NameValuePair has an attribute Value.

If there are multiple elements with the correct name and you want to check if any of them has the value "One", you can use the exist(...) method:

where [XmlDataColumn].exist(
  '/NameValuePairCollection/NameValuePair[@Name="Subject" and @Value="One"]') = 1

Answer 2

Another option is to cast the XML as varchar, and then search for the given string as if the XML vas a varchar field.

SELECT * 
FROM Table
WHERE CAST(Column as nvarchar(max)) LIKE '%Name="Foo" Value="One"%'

I love this solution as it is clean, easy to remember, hard to mess up, and can be used as a part of a where clause.

It's not as dynamic and complete as the answer provided by Leo, but depending on the circumstances it can be a "quick n' dirty" way to get the data needed.