Sum XML Value based on different attribute types

Question

I'm trying to extract number of adults, children and infants from the below example XML:

Adults = AgeType 3
Children = AgeType 2
Infants = AgeType 1

and then count specifies how many of that age type. Correct result is:

35 Adults
5 Children
2 Infants

The below code returns the correct result, however I would like to avoid the cross apply entering to the <Customer> tags (as there are hundreds of millions per day).

declare @a table(a xml)
insert into @a  
select '<Customers>
        <Customer AgeType="3" Count="10" />    
        <Customer AgeType="3" Count="20" />
        <Customer AgeType="3" Count="5" />       
        <Customer AgeType="2" Count="5" />
        <Customer AgeType="1" Count="2" />
        </Customers>'

select  
    sum(case when b.cust = 3 then b.cust*b.count_cust end)/3 as adt
    ,sum(case when b.cust = 2 then b.cust*b.count_cust end)/2 as chd
    ,sum(case when b.cust = 1 then b.cust*b.count_cust end)/1 as inf
from   (
select c.value('(@AgeType)','int') As cust
,c.value('(@Count)','int') As count_cust
from @a cross apply a.nodes('Customers/Customer') as t(c)
) as b

Can anyone find any other logic that could be more effiecient? I was thinking using count or sum over the <Customer> tags like below, however I can't get the correct reults.

 a.value('count(Customers/Customer/@AgeType[.=3])','int') As adt
,a.value('count(Customers/Customer/@AgeType[.=2])','int') As chd
,a.value('count(Customers/Customer/@AgeType[.=1])','int') As inf

DC07 · Accepted Answer · 2018-09-26 10:08:28Z

1

Change your XPath from

count(Customers/Customer/@AgeType[.=3])

to

sum(Customers/Customer[@AgeType = 3]/@Count)

to sum up all adult (@AgeType = 3) Customer @Count attribute values.

Follow the same pattern for the other age types.

edited Sep 26, 2018 at 10:08

DC07

3016 silver badges20 bronze badges

answered Sep 25, 2018 at 15:47

kjhughes

113k31 gold badges198 silver badges276 bronze badges

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

2 Comments

DC07 Over a year ago

Hi, works as expected. i only had to change count(Customers/Customer[@AgeType = 3]/@Count) to use sum instead sum(Customers/Customer[@AgeType = 3]/@Count)

kjhughes Over a year ago

@DC07: Oh, yes, of course. Sorry about that, and thanks for fixing it.

Collectives™ on Stack Overflow

Sum XML Value based on different attribute types

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related