2

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
Andrea
  • 11,801
  • 17
  • 65
  • 72
DC07
  • 293
  • 5
  • 18

1 Answers1

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.

DC07
  • 293
  • 5
  • 18
kjhughes
  • 106,133
  • 27
  • 181
  • 240
  • 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) – DC07 Sep 26 '18 at 08:36
  • @DC07: Oh, yes, of course. Sorry about that, and thanks for fixing it. – kjhughes Sep 26 '18 at 11:38