Travels of the Secretary of State, grouped by secretary, showing duration of visits in a country, using XQuery

01-secretary-of-state-travels-to-a-country.xq

xquery version "3.0";

let $results := 
    for $trip in collection('/db/cms/apps/travels/data/secretaries')/trips/trip[country/@id = 'israel']
    let $duration := days-from-duration(sum($trip ! (./xs:date(end-date) - ./xs:date(start-date) + xs:dayTimeDuration('P1D'))))
    group by $secretary := $trip/name
    return
        <trips secretary="{$secretary}" trips="{count($trip)}" days="{$duration}">{
            $trip !
                <trip>{
                    (:<country>{./country/string()}</country>, :)./locale, ./remarks, ./start-date, ./end-date, <days>{days-from-duration((./xs:date(end-date) - ./xs:date(start-date) + xs:dayTimeDuration('P1D')))}</days>
                }</trip>
        }</trips>
let $ordered-trips := 
    for $trips in $results
    order by $trips/trip[1]/start-date
    return
        $trips
return
    <results>
        <summary>{
            $ordered-trips ! <trips>{./@*}</trips>
        }</summary>
        {
            $ordered-trips
        }
    </results>

02-show-results-as-table.xq

xquery version "3.0";

let $summary := 
    <summary>
        <trips secretary="John Foster Dulles" trips="1" days="2"/>
        <trips secretary="William Pierce Rogers" trips="1" days="3"/>
        <trips secretary="Henry A Kissinger" trips="15" days="79"/>
        <trips secretary="Cyrus Roberts Vance" trips="9" days="28"/>
        <trips secretary="Alexander Meigs Haig" trips="3" days="6"/>
        <trips secretary="George Pratt Shultz" trips="16" days="34"/>
        <trips secretary="James Addison Baker" trips="10" days="27"/>
        <trips secretary="Warren Minor Christopher" trips="34" days="79"/>
        <trips secretary="Madeleine Korbel Albright" trips="9" days="26"/>
        <trips secretary="Colin Luther Powell" trips="7" days="16"/>
        <trips secretary="Condoleezza Rice" trips="25" days="62"/>
        <trips secretary="Hillary Rodham Clinton" trips="5" days="12"/>
        <trips secretary="John Forbes Kerry" trips="9" days="22"/>
    </summary>
return
    <table>
        <tr>
            <th>Secretary</th>
            <th>Trips to Israel</th>
            <th>Days</th>
        </tr>
        {
            $summary/trips !
            <tr>{
                ./@* ! <td>{./string()}</td>
            }</tr>
        }
    </table>