CODEWITHSUNDEEP
sql-serverxml
Pawel Czapski
Pawel Czapski

Reputation: 1864

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

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.

Upvotes: 1

Views: 1298

Answers (1)

har07
har07

Reputation: 89295

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

Upvotes: 1

Related Questions