LIKE comparison in SQL Server XML data type

Question

I have a DB table with an XML data type column. The column contains the following values:

<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 column of the XML data type is set to "One" for "Foo" . I had a problem creating the appropriate XQuery to filter the results.

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

How can I query the value in SQL Server XML column
Use LIKE record in SQL Server XML format

Here is the SQL that 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 that 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 does not work. 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 checks to see if the NameValuePair attribute has a Value attribute.

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

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

Answer 2

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

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

I like this solution because it is clean, easy to remember, hard to mix up and can be used as part of the where clause.

This is not as dynamic and complete as the answer provided by Leo, but depending on the circumstances, it can be a “quick” dirty way to get the necessary data.