5

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 *'
Community
  • 1
  • 1
Jesse Webb
  • 43,135
  • 27
  • 106
  • 143
  • Possible duplicate of [Use a LIKE statement on SQL Server XML Datatype](https://stackoverflow.com/questions/1832987/use-a-like-statement-on-sql-server-xml-datatype) – Squazz Sep 06 '17 at 11:43

2 Answers2

3

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
Jesse Webb
  • 43,135
  • 27
  • 106
  • 143
Leo Wörteler
  • 4,191
  • 13
  • 10
  • I tried this but it still didn't work. See the edit to my question for more details. – Jesse Webb Apr 10 '13 at 20:11
  • Are there multiple `NameValuePair`s with the `Name` you are looking for? I'll update my answer for that case. – Leo Wörteler Apr 10 '13 at 20:26
  • I don't think there were multiple elements with the same `Name` but I will try your alternate solution and see what happens. – Jesse Webb Apr 10 '13 at 20:32
  • I must have records where there are multiple `NameValuePair` elements that have a "Name" attribute of `"Foo"` because your second solution worked for me. Thank you very much for you help! – Jesse Webb Apr 10 '13 at 20:41
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.

Squazz
  • 3,912
  • 7
  • 38
  • 62