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.

marc_s · Accepted Answer · 2009-07-31 13:36:21Z

16

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

edited Jul 31, 2009 at 13:36

answered Jul 31, 2009 at 13:29

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

GordonBy Over a year ago

It is..... You're a star. Cheers :) Don't suppose you can point me in the direction of any decent resources to read up on SQL XML manipulation???

marc_s Over a year ago

Glad I could help, Gordon! The best SQL XML DML article I found so far is pretty old, but still very useful: 15seconds.com/Issue/050803.htm

marc_s Over a year ago

Or this here maybe: sqlservercentral.com/Articles/SQL+Server+2005/XML

Collectives™ on Stack Overflow

Working with the SQL Server XML data type

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related