CODEWITHSUNDEEP
xmlxpath
StevenU
StevenU

Reputation: 39

XPATH sum function

Is there a way to use the sum function in XPATH, so it will work on sub-nodes in the XML file?

Given the following XML file.

<?xml version='1.0' encoding='UTF-8'?>
<countries>
    <country name ='Israel'>
        <city num = '300001'>'TelAviv'</city>
        <city num = '300000'>'Jerusalem'</city>
        <city num = '400000'>'Haifa'</city>
    </country>
    <country name ='USA'>
        <city num = '800000'>'Los Angeles'</city>
        <city num = '500000'>'New York'</city>
        <city num = '1000'>'Washington'</city>
    </country>
    <country name ='Italy'>
        <city num = '998999'>'Rome'</city>
        <city num = '1000'>'Vatican'</city>
    </country>
    <country name ='LalaLand'>
        <city num = '100'>'A'</city>
        <city num ='200'>'B'</city>
    </country>
</countries>

The task is to return the names of all countries that the sum of all residents (num attribute) in all their countries is greater than 1 million. After several attempts, I was able to write the following query:

countries/country[sum(//country/city/@num) > 1000000]/@name

But the query summed up all citizens (num) in all countries together, not what we were asked to do. (What is returned from the query is 3300300)

We should get Israel and USA, both populations are more than 1 million.

Suggestions for optimization and correct solution?

Upvotes: 2

Views: 892

Answers (1)

Robby Cornelissen
Robby Cornelissen

Reputation: 97152

You need to sum only the cities under the current country node (./city) instead of the cities under all country nodes (//country/city):

countries/country[sum(./city/@num) > 1000000]/@name

Output:

Attribute='name=Israel'
Attribute='name=USA'

Upvotes: 2

Related Questions