Summing Xml Data (concisely?)

• bstauffer-926504

  SSC Enthusiast

  Points: 129

  More actions

  July 20, 2009 at 7:29 am

  #136712

  I have an xml column in table MyTable called xData_1.

  Please see attached xml text file for the data contained in the table. It's simple stuff.

  I want to look at some specific xml within each row and sum it to a single value.

  I've tried a lot of things but I'm not getting any closer. Anyone have any ideas? Thanks!

  -- query -- data is in attachment

  select xData_1.query('

  for $x in /items/item

  where $x/@year=2009 and $x/@month=6

  return sum($x/@amount)

  ') from MyTable

  -- MyTable schema

  CREATE TABLE MyTable (xData_1 xml)

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  July 20, 2009 at 7:59 am

  #1026954

  Maybe this

  select xData_1.value('

  sum(for $x in /items/item

  where $x/@year=2009 and $x/@month=6

  return $x/@amount)','int')

  from MyTable

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• bstauffer-926504

  SSC Enthusiast

  Points: 129

  More actions

  July 20, 2009 at 8:43 am

  #1026997

  Thanks Mark! That didn't quite get all the way there because it still returned 12 rows but it DID give me the push I needed to crack it. 😀

  select sum(xData_1.value('

  sum(for $x in /items/item

  where $x/@year=2009 and $x/@month=6

  return $x/@amount)','float'))

  from MyTable