I need XML SQL query to get sum of nodes value based on another node value

Question

I am struggling with this all day and still do not have desired output. I need a query as in title.

<aa>
  <bb>
    <cc>
      <typeid>2</typeid>
      <dd>
        <amount>10</amount>
      </dd>
    </cc>
    <cc>
      <typeid>2</typeid>
      <dd>
        <amount>20</amount>
      </dd>
    </cc>
  </bb>
</aa>

So my desire output would be 30, but it has to be based on the value type id, so pseudo code would be select sum(node to amount) from XML where (typeid node) = 2

I have tried:

SELECT t1.c1.value('sum(Amount)', 'float')
FROM @xmlCase.nodes('//aa/bb/cc') as t(c)
cross apply t.c.nodes('dd') as t1(c1)
WHERE t.c.value('typeId[1]', 'int') = 2

But in this case I have received two values: 10 and 20, but I need sum of those values. Thank you.

har07 · Accepted Answer · 2015-05-16 05:55:54Z

1

One possible way :

declare @xmlCase XML = '<aa>
  <bb>
    <cc>
      <typeid>2</typeid>
      <dd>
        <amount>10</amount>
      </dd>
    </cc>
    <cc>
      <typeid>2</typeid>
      <dd>
        <amount>20</amount>
      </dd>
    </cc>
  </bb>
</aa>'

SELECT x.value('sum(cc[typeid=2]/dd/amount)','float') as 'total'
FROM @xmlCase.nodes('//bb') bb(x)

Basically above query groups result at <bb> element level, and select sum of <amount> elements where corresponding <typeid> element equals 2.

Output :

enter image description here

SQL Fiddle

edited May 16, 2015 at 5:55

answered May 14, 2015 at 9:59

har07

89.5k12 gold badges87 silver badges143 bronze badges

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

1 Comment

Pawel Czapski Over a year ago

Wow, that works very well, my believing in stockoverflow is back because of you. Thank you very much @har07.

Collectives™ on Stack Overflow

I need XML SQL query to get sum of nodes value based on another node value

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related