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?

//iati-activity[not(participating-org/@role='Implementing')]

Which activities are missing Commitments?

//iati-activity[not(transaction/transaction-type/@code='C')]

Counting distinct activities

Count number of activities

count(//iati-activity)

Count distinct IATI Identifiers

count(distinct-values(//iati-activity/iati-identifier))

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

count(distinct-values(//iati-activity[reporting-org/@ref='SE-6']/iati-identifier))

Finding distinct IATI identifiers

Find duplicate IATI identifiers in a group of iati-activities

//iati-activities/iati-activity[iati-identifier = preceding-sibling::iati-activity/iati-identifier]/iati-identifier

Return the XML of activities found to be duplicate by identifier (only the duplicates, not the originals)

//iati-activities/iati-activity[iati-identifier = preceding-sibling::iati-activity/iati-identifier]

Dates

Smallest start date

min(for $d in //activity-date[@type="start-planned" or @type="start-actual"]/@iso-date where ($d != '') return xs:date($d))

Budgets

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