CODEWITHSUNDEEP
sql-serverxpath
livnt
livnt

Reputation: 45

How to sum all xml nodes values, in the deepest hierarchy of the doc, using xpath to xml column in sql server, without using nodes names?

I have an xml field in sql server, and I want to sum all nodes values in the deepest level, using xpath.

For example, in this scheme:

<c>
  <b1>
    <a>1</a>
    <d>4</d>
    <g>5</g>
 </b1>
 <b1>
   <a>7</a>
   <d>1</d>
   <g>2</g>
 </b1>
</c>

I need to get the sum of all node values under "b1". All nodes names may change from one xml to another, and also the location of "b1" in the hierarchy level.

So basically, I need to be able to get to the deepest node, and all its siblings, and sum all.

In this example, the sum I expect to get is: 20.

I need the xpath expression look like this:

xmlData.value('sum(.......)', 'float')

And one more thing: Is there a way to eliminate a certain node from the sum? Say I want all, except node "a"..

Upvotes: 0

Views: 295

Answers (1)

Rubens Farias
Rubens Farias

Reputation: 57956

You can use a generic xpath to select all nodes and, later, cast it as int:

DECLARE @data XML = '
        <NewDataSet>
          <b1>
            <a>1</a>
            <d>4</d>
            <g>5</g>
         </b1>
         <b1>
           <a>7</a>
           <d>1</d>
           <g>2</g>
         </b1>
        </NewDataSet>'

SELECT  SUM(t.c.value('(text())[1]', 'INT'))    -- cast their value as INT
FROM    @data.nodes('//*') t(c)                 -- all elements, no matter their name

EDIT:

select  @data.value('sum(//*[not(*)])', 'float')

Upvotes: 2

Related Questions