Eliminate a xml node from sum, in XPath to SQL Server XML column

Question

I have this XPath expression, which sum all nodes in the deepest XML hierarchy, without using nodes names:

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

How do I make an exception of one node, by its name?

Say this is the xml:

<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 would like the sum to contain "d" and "g", without "a", but "a" will be pass as parameter and so need to be represented as parameter inside the expression. I've tried the following:

declare @except varchar(max) = 'a'


select  @data.value('sum(//*[not(*)])', 'float') - @data.value('sum(//*:local-name()=sql:variable("@except"))', 'float')

but no success.

DhruvJoshi · Accepted Answer · 2018-08-26 07:30:34Z

2

While Marc's answer is what i would do mostly, I have a small hack for you, below

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

answered Aug 26, 2018 at 7:30

DhruvJoshi

17.2k6 gold badges46 silver badges63 bronze badges

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

2 Comments

livnt Over a year ago

Thank you, it works fine. I added just another little requirement. Be happy if you would adjust by it.

livnt Over a year ago

I think there's no place for creating another question on this. Please try to adjust your answer by my last requirement. Thanks

marc_s · Accepted Answer · 2018-08-26 07:26:24Z

1

You could parse the XML into name and value in a CTE, and then select from that CTE - something like this:

DECLARE @Data XML = '...(your XML here).....';

;WITH XmlCte AS
(
    SELECT
        NodeName = xc.value('local-name(.)', 'varchar(25)'),
        NodeValue = xc.value('(.)[1]', 'int')
    FROM 
        @Data.nodes('/c/b1/*') AS XT(XC)
)
SELECT SUM(x.NodeValue)
FROM XmlCte x
WHERE x.NodeName <> 'a'

answered Aug 26, 2018 at 7:26

marc_s

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

Collectives™ on Stack Overflow

Eliminate a xml node from sum, in XPath to SQL Server XML column

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related