XQuery - List name and population of the most populous country on each continent

Question

I have to do the following exercise but I don't know do it.

List the name and population of the most populous country on each continent. It is forbidden to use the ids of the 5 continents in the query, ie neither “f0_119”, nor “f0_123”, nor “f0_126”, nor “f0_129”, nor “f0_132” can appear in the query.

Here you have the link to download the XML file: https://drive.google.com/file/d/1a_YYqW-uWYtGNBuMqqAiMu4vElGJbYCx/view?usp=sharing

This is my code. I've got China but I need to show the most populous countries of the other continents Russia, the United States... Can someone help me, please?

for $var in /mondial/country
let $max:= max(/mondial/country/@population)
where $var/@population = $max
return concat(data($var/name), ' - ',data($var/@population))

Yitzhak Khabinsky · Answer 1 · 2020-05-03T23:55:15.123

@pepe123656, you didn't specify what XQuery version you are using. Here is XQuery 3.0 and later solution that is using group by clause.

encompassed[...] predicate is used because some countries like Russia, Turkey, and the like, are on two continents.

XQuery

<root>{
    for $country in doc('e:\temp\mon.xml')/mondial/country
    let $population := $country/@population
    (: Turkey, Russia, etc. are on two continents :)
    (: We select the highest land percentage continent :)
    group by $continentID := $country/encompassed[xs:double(@percentage) eq max($country/encompassed/@percentage)]/@continent
    order by $continentID
    return <row>
      {
        $country/../continent[@id = $continentID]/@name || ": " || $country[@population = max($population)]/@name || ": " || xs:integer(max($population))
      }
      </row>
}</root>

Output

<root>
  <row>Europe: Germany: 83536112</row>
  <row>Asia: China: 1210004992</row>
  <row>America: United States: 266476272</row>
  <row>Australia/Oceania: Australia: 18260864</row>
  <row>Africa: Nigeria: 103912488</row>
</root>

XQuery - List name and population of the most populous country on each continent

1 Answers1