Working with the SQL Server XML data type

Question

I've got a table which has a XML field. The typical XML it contains is;

<things>
  <Fruit>
    <imageId>39</imageId>
    <title>Apple</title>
  </Fruit>
  <Fruit>
    <imageId>55</imageId>
    <title>Pear</title>
  </Fruit>
  <Fruit>
    <imageId>76</imageId>
    <title>Grape</title>
  </Fruit>
</things>

In my table i've got around 50 rows, i'm only concerned with two fields, omId (int primary key) and omText (my xml data).

What i'm trying to achieve is a way of saying, across all xml data in the whole table... give me all of the xmlElements where the title is X. Or give me a count of all items that use an imageId of 55.

I'm using the XML data type VALUE and QUERY functions to retrieve the data.

   select omID,
   omText.query('/things/Fruit')
   ,cast('<results>' + cast(omText.query('/things/Fruit') as varchar(max)) + '</results>' as xml) as Value
   from dbo.myTable
   where omText.value('(/things/Fruit/imageId)[1]', 'int') = 76

Which only works where the id i'm searching for is the first one in the document. It doesn't seem to search all of the xml.

Fundamentally the resultset comes back with one row for each entry in the TABLE, wheras i think i need to have one row for each matched ELEMENT... Not exactly sure how to start writing a group-by for this tho.

I'm starting to feel like i'm making this harder than it needs to be...... thoughts & ideas please.

Answer 1

What i'm trying to achieve is a way of saying, across all xml data in the whole table... give me all of the xmlElements where the title is X.

Not sure if I totally understood your question here - or are you looking for this? You would grab all the /things/Fruit elements a "nodes" and cross join them against your "base data" in the myTable - the result would be one row per XML element in your XML data field:

select 
   omID,
   T.Fruit.query('.')
from 
   dbo.myTable
cross apply
   omText.nodes('/things/Fruit') as T(Fruit)
where 
   T.Fruit.value('(title)[1]', 'varchar(50)') = 'X'

Or give me a count of all items that use an imageId of 55.

select 
   count(*)
from 
   dbo.myTable
cross apply
   omText.nodes('/things/Fruit') as T(Fruit)
where 
   T.Fruit.value('(imageId)[1]', 'int') = 55

Is that what you're looking for?

Marc