sum two xml nod with one name but different attribute

Question

I have an sql query. I need to sum two xml nodes with the same names () but different attributes (ident= "1cat" + "3cat"). I can get nodes by their number ([1] and [3]) but I need to sum them by "ident". How can I sum 1Category and 3Category by their idents?

DECLARE @xml XML  
SET @xml = 
'<cat:catalog xmlns:cat="http://datypic.com/cat" xmlns:prod="http://datypic.com/prod">
  <cat:number ident="1Category">10</cat:number>
  <cat:number ident="2Category">20</cat:number>
  <cat:number ident="3Category">30</cat:number>
</cat:catalog>'; 

WITH XMLNAMESPACES (
'http://datypic.com/cat' AS cat
)
SELECT 
    c.c.value('(cat:number/text())[1]', 'INT') '1Category',
    c.c.value('(cat:number/text())[3]', 'INT') '3Category'
FROM @xml.nodes('cat:catalog') c(c)

podiluska · Accepted Answer · 2012-09-24 09:30:52Z

1

WITH XMLNAMESPACES ( 
'http://datypic.com/cat' AS cat 
) 
SELECT  
    c.c.value('(cat:number[@ident="1Category"])[1]', 'INT') +
    c.c.value('(cat:number[@ident="3Category"])[1]', 'INT') 
FROM @xml.nodes('cat:catalog') c(c)

answered Sep 24, 2012 at 9:30

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

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

1 Comment

JohnyMotorhead Over a year ago

yes man!! You're saving me 2nd time. that's what I was needed!! thank a lot!!

Mikael Eriksson · Accepted Answer · 2012-09-24 09:39:31Z

1

WITH XMLNAMESPACES ( 
'http://datypic.com/cat' AS cat 
) 
SELECT @xml.value('sum(/cat:catalog/cat:number[@ident=("1Category", "3Category")]/text())', 'INT')

answered Sep 24, 2012 at 9:39

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Collectives™ on Stack Overflow

sum two xml nod with one name but different attribute

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related