Useful XQuery queries¶
XQuery is a query language for querying XML files. This page provides some useful example queries for working with IATI data. A good graphical interface for running these queries is BaseX.
Many of the queries below are also valid XPath, since XPath is a subset of XQuery.
Activities missing information¶
Which activities are missing the Implementing Org?
Which activities are missing Commitments?
Counting distinct activities¶
Count number of activities
Count distinct IATI Identifiers
Percentage of activities with unique activity identifiers
count(distinct-values(//iati-activity/iati-identifier)) div count(//iati-activity/iati-identifier) * 100
Count distinct IATI Identifiers for a given reporting organisation
Smallest start date
min(for $d in //activity-date[@type="start-planned" or @type="start-actual"]/@iso-date where ($d != '') return xs:date($d))
Find budgets that have start dates after a certain date (replace yyyymmdd with year, month, and day. e.g. 1st Sept. 2013 would be 20130901])
//budget[number(translate(period-start/@iso-date,'-','')) > yyyymmdd]
Find the activities that have a budget with a start date after a certain date (append /.. )
//budget[number(translate(period-start/@iso-date,'-','')) > yyyymmdd]/..
Find the iati-identifiers of budgets that have start dates after a certain date
//budget[number(translate(period-start/@iso-date,'-','')) > yyyymmdd]/..//iati-identifier