XQuery join result

Question

I have a XML that looks like this:

<?xml version="1.0"?>
<root>
  <flight>
    <number>10001</number>
    <airport>LAX</airport>
    <dest>
      <airport>SFO</airport>
    </dest>
  </flight>
  <flight>
    <number>10002</number>
    <airport>LAX</airport>
    <dest>
      <airport>JFK</airport>
    </dest>
  </flight>
  <flight>
    <number>10003</number>
    <airport>JFK</airport>
    <dest>
      <airport>LAX</airport>
    </dest>
  </flight>
</root>

Using XQuery I need to get something like this:

<res>
    <airport code="LAX">
        <deps>2</deps>
        <dests>1</deps>
    </airport>
    <airport code="JFK">
        <deps>1</deps>
        <dests>1</deps>
    </airport>
    <airport code="SFO">
        <deps>0</deps>
        <dests>1</deps>
    </airport>
</res>

I did it, and can get the correct result, however, my query can only find deps or dests, but not both.

Here is how I approached the problem.

let $all := doc("flights.xml")/root
for $airports in distinct-values($all/flight//*/airport) (:here I get all airport codes:)
order by $airports 

for $nr-dep in $all/flight/airport

where $nr-dep = $airports 
group by $airports 

return <res>
          <airport name="{$airports}"><deps>{count($nr-dep)}</deps></airport>
       </res>

Here I get the departure count. I can easily get destionations by replacing for $nr-dep in $all/flight/airport with for $nr-dep in $all/flight/dest/airport however I cannot find a way to show both at the same result like the expected XML.

Leo Wörteler · Answer 1 · 2016-03-14T13:54:03.170

4

Here is a version using group by:

<res>{
    for $airport in //airport
    group by $code := $airport/text()
    order by $code
    return <airport code="{$code}">
        <deps>{ count($airport/parent::flight) }</deps>
        <dests>{ count($airport/parent::dest) }</dests>
    </airport>
}</res>

edited Mar 14 '16 at 13:54

answered Mar 14 '16 at 13:05

Leo Wörteler

4,191
13
10

1

I guess being explicit about the sort order after grouping would be a good thing. From reading the spec I don't think a certain order is guaranteed by `group by`. – Tomalak Mar 14 '16 at 13:20
Good point, I overlooked that in the original query. I will update my answer. – Leo Wörteler Mar 14 '16 at 13:52
I think that's more elegant than my solution. – Tomalak Mar 14 '16 at 15:31

score 3 · Accepted Answer · answered Mar 14 '16 at 12:51

3

Why not simply:

for $airport in distinct-values($all//airport)
order by $airport
return <airport code="{$airport}">
  <deps>{count($all//flight/airport[. = $airport])}</deps>
  <dests>{count($all//dest/airport[. = $airport])}</dests>
</airport>

answered Mar 14 '16 at 12:51

Tomalak

332,285
67
532
628

It works, however, I am still getting started with XQuery and cannot understand all the details behind this and what makes it work. I don't understand `//` and `[. = $airport]`. Thank you very much. – Idrizi.A Mar 14 '16 at 13:36
1

`//` causes a recursive tree search, i.e. *"all descendants, not only children"*. It's shorthand for `descendant-or-self::node()`, and it's XPath, not XQuery. `[. = $value]` is XPath as well, it means *"if this node has the text value of `$value`"*. Seems you need to do some reading on XPath first. :) – Tomalak Mar 14 '16 at 13:42

XQuery join result

2 Answers2