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))divcount(//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 $din//activity-date[@type="start-planned"or@type="start-actual"]/@iso-datewhere($d!='')returnxs: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