some selections from IATI files - suitable for creating dashboards etc

IATI-select-1.xquery

xquery version "3.0";

declare namespace functx = "http://www.functx.com"; 
declare function functx:sort 
  ( $seq as item()* )  as item()* {
       
   for $item in $seq
   order by $item
   return $item
 } ;

declare function local:select-test2(){
let $db := db:open('DFID-IATI')
let $output := for $project in subsequence($db//iati-activity[reporting-org/@ref='GB-1'][@hierarchy=1],1,4)
let $id := $project/iati-identifier
let $general_descriptors := ($project/title,
            $project/description,
            $project/iati-identifier,
            $project/reporting-org,
            $project/activity-status
            )
let $agg_budget := try {
$db//iati-activity[reporting-org/@ref='GB-1'][@hierarchy=2][related-activity/@ref=$id]/budget/value
}
catch *
{()}

let $agg_sectors := try {
$db//iati-activity[reporting-org/@ref='GB-1'][@hierarchy=2][related-activity/@ref=$id]/sector
}
catch *
{()}
(:return <project>{$agg_sectors[.=distinct-values($agg_sectors)]}</project>:)

return (<general>{$general_descriptors}</general>,<budget>{xs:integer(sum($agg_budget))}</budget>,<sectors>{$agg_sectors}</sectors>)

return (<output>{$output}</output>,
<unique-sectors-list-ordered>{functx:sort(distinct-values($output//sector) ! element sector {.})}</unique-sectors-list-ordered>
)
};

local:select-test2()