CODEWITHSUNDEEP
sql-serverxmlxpathxqueryxquery-sql
DC07
DC07

Reputation: 303

Sum XML Value based on different attribute types

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

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

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

Upvotes: 2

Views: 269

Answers (1)

kjhughes
kjhughes

Reputation: 111621

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.

Upvotes: 1

Related Questions