sql-server - 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

标签： sql-serverxmlxpathxqueryxquery-sql

Answer 1

更改您的 XPath

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

至

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

总结所有成人（@AgeType= 3）Customer @Count属性值。

对其他年龄类型遵循相同的模式。