CaliforniaBuckeye
New Member
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?\[code\]DECLARE @xml XML SET @xml = '<cat:catalog xmlns:cat="http://datypic.com/cat" xmlns
rod="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)\[/code\]
rod="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)\[/code\]