Joining elements in separate sequences based on their position in Xquery 3.0

Question

I have 2 sequences as shown below:

let $city := ('London', 'Tokyo')
let $country := ('England', 'Japan')
let $region := ('Europe','Asia')

I would like to do is manipulate the 2 sequences so that it appears as so:

<Data>
    <location>
        <city>London</city>
        <country>England</country>
        <region>Europe</region>
    </location>
    <location>
        <city>Tokyo</city>
        <country>Japan</country>
        <region>Asia</region>
    </location>  
</Data>

My plan was to do the following:

1) Add a count to each of the $city, $country and $region sequences respectively as shown below. Please note that I haven't detailed how I'd be doing this but I believe it should be fairly straightforward.

let $city := ('1London', '2Tokyo')
let $country := ('1England', '2Japan')
let $region := ('1Europe','2Asia')

2) Join on the first character of each item in the sequence and manipulate it somehow as shown below. Please note this code doesn't work but it's what I believe could work.

let $city := ('1London', '2Tokyo')
let $country := ('1England', '2Japan')
let $region := ('1Europe','2Asia')

for $locCity in $city, $locCountry in $country, $locRegion in $region
where substring($locCity,1,1) = substring($locCountry ,1,1) and 
substring($locCountry ,1,1) = substring($locRegion ,1,1)

let $location := ($locCity,$locCountry,$region)

return 
$location

However, this is not working at all. I'm not sure how to proceed really. I'm sure there is a better approach to tackling this problem. Perhaps a "group by" approach might work. Any help would be greatly appreciated. Thanks.

Cheers Jay

Mads Hansen Mads Hansen · Accepted Answer · 2017-08-06T03:41:14

Find out which of the sequences has the most items using max(), then iterate from 1 to max() and construct a <location> element for each. Select the values from each sequence using a predicate select by position:

xquery version "1.0";
let $city := ('London', 'Tokyo')
let $country := ('England', 'Japan')
let $region := ('Europe','Asia')
return
  <Data>
  {
  for $i in 1 to max((count($city), count($country), count($region)))
  return
    <location>
        <city>{ $city[$i] }</city>
        <country>{ $country[$i] }</country>
        <region>{ $region[$i] }</region>
    </location>
  }
  </Data>

Joining elements in separate sequences based on their position in Xquery 3.0

1 Answers