XQuery/Searching multiple collections

Motivation

You want to find records in multiple collections.

Method

There are several ways to do this. The simplest way is to put both collections in a parent collection and start your search at the parent.

Lets assume you have three collections:

  /db/test
  /db/test/a
  /db/test/b

To get all the books in both collection a and b just specify the parent collection which is /db/test

  for $book in collection('/db/test')//book

Note that the double forward slash // will find the books anywhere in the base collection or any child collections.

If you have two collections that are at different locations in a file system you can simply specify each collection and join them together using the sequence join operation. This is the default operation of enclosing two sequences in parenthesis. For example if you have two sequences, a and b, the concatenation of the two sequences is just (a,b).

Assume you have two collections that have books in the following collections:

Collection A

File='/db/test/a/books.xml'

<books>
    <book id="47">
        <title>Moby Dick</title>
        <author>Herman Melville</author>
        <published-date>1851-01-01</published-date>
        <price>$19.95</price>
        <review>The adventures of the wandering sailor in pursuit of a 
            ferocious wale.</review>
    </book>
    <book id="48">
        <title>The Great Gatsby</title>
        <author>F. Scott Fitzgerald</author>
        <published-date>1925-05-10</published-date>
        <price>$29.95</price>
        <review>Chronicles of an era during the roaring 1920s
                when the US economy soared.</review>
    </book>
</books>

Collection B

File='/db/test/b/books.xml'

<books>
    <book id="49">
        <title>Catch-22</title>
        <author>Joseph Heller</author>
        <published-date>1961-01-01</published-date>
        <price>$19.95</price>
        <review>A satirical, historical novel set during the later stages of World War II from 1943 onwards.</review>
    </book>
    <book id="48">
        <title>Lolita</title>
        <author>Vladimir Nabokov</author>
        <published-date>1955-01-01</published-date>
        <price>$19.95</price>
        <review>A man becomes obsessed with a 12-year-old girl.</review>
    </book>
</books>

The following query would operate on both collections.

xquery version "1.0";

let $col-a := '/db/test/a'
let $col-b := '/db/test/b'
return
<books>{
for $book in (collection($col-a)//book, collection($col-b)//book)
return
  $book
}</books>

If you wanted to only return the titles you could use the following:

xquery version "1.0";

let $col-a := '/db/test/a'
let $col-b := '/db/test/b'
return
<books>{
for $book in (collection($col-a)//book, collection($col-b)//book)
return
  $book/title
}</books>

This would return the following results:

<books>
<title>Moby Dick</title>
<title>The Great Gatsby</title>
<title>Catch-22</title>
<title>Lolita</title>
</books>