Basic queries

All possible column names can be found by querying a table and looking at the returned JSON.
By specifying the xpath of an element in root, you can filter results to within that element without displaying the entire xml.

Examples

This gets you data within the budget element from a random activity.

select *
from xson where root='/iati-activities/iati-activity/budget'
limit 1;

Result

{
    result: [
        {
            aid: "SE-0-SE-6-10451A0101-MLI-72012",
            pid: "SE-0",
            root: "/iati-activities/iati-activity/budget",
            xson: {
                /value: 189381.7616514557,
                /value@currency: "USD",
                /value@value-date: "2019-01-01",
                /period-end@iso-date: "2019-12-28",
                /period-start@iso-date: "2019-01-01"
            }
        }
    ],
    duration: 0.014
}

This gets you data within the total-expenditure element from a random organisation file.

select *
from xson where root='/iati-organisations/iati-organisation/total-expenditure'
limit 1;

Result

{
    result: [
        {
            aid: null,
            pid: "XM-DAC-41123",
            root: "/iati-organisations/iati-organisation/total-expenditure",
            xson: {
                /value: 1060488055.37,
                /value@currency: "USD",
                /value@value-date: "2013-01-01",
                /period-end@iso-date: "2013-12-31",
                /period-start@iso-date: "2013-01-01"
            }
        }
    ],
    duration: 0.009
}