Combining and grouping data from two XML files

Question

I have an XML file bands.xml as below:

<?xml version="1.0" encoding="ISO-8859-1"?>
<bands>
  <band>
    <name>Metallica</name>
    <nationality>American</nationality>
  </band>
  <band>
    <name>Marilyn Manson</name>
    <nationality>American</nationality>
  </band>
</bands>

and another file listing their albums albums.xml as below:

<?xml version="1.0" encoding="ISO-8859-1"?>
<albums>
  <album>
    <title>Master of Puppets</title>
    <band>Metallica</band>
    <date>1986</date>
    <genre>rock</genre>
  </album>
  <album>
    <title>St. Anger</title>
    <band>Metallica</band>
    <date>2003</date>
    <genre>rock</genre>
  </album>
  <album>
    <title>The Golden Age of Grotesque</title>
    <band>Marilyn Manson</band>
    <date>2004</date>
    <genre>rock</genre>
  </album>
  <album>
    <title>Mechanical Animals</title>
    <band>Marilyn Manson</band>
    <date>1998</date>
    <genre>pop</genre>
  </album>
</albums>

What I wish to do is combine these 2 XML files into another processed XML file. The Xquery will list down all bands, and within it list all the albums associated with that particular band, group it by album genre (sorted alphabetically). This is further illustrated as in the XML file below:

<?xml version="1.0" encoding="ISO-8859-1" ?>
<bands>
  <band>
    <name>Metallica</name>
    <nationality>American</nationality>
    <albums genre="rock">
      <album date="1986">
        <title>Master of Puppets</title>
      </album>
      <album date="2003">
        <title>St. Anger</title>
      </album>
    </albums>
  </band>
  <band>
    <name>Marilyn Manson</name>
    <nationality>American</nationality>
    <albums genre="pop">
      <album date="1998">
        <title>Mechanical Animals</title>
      </album>
    </albums>
    <albums genre="rock">
      <album date="2004">
        <title>The Golden Age of Grotesque</title>
      </album>
    </albums>
  </band>
</bands>

What I've managed to do is get all the band details part, and also list all associated albums produced by that band. However since I am using Xquery 1.0, it is really frustrating to group the albums according to genre!

Answer 1

The following should work using purely XQuery 1.0:

declare variable $bandsxml  := doc("bands.xml");
declare variable $albumsxml := doc("albums.xml");
<bands>
{
  for $findband in $bandsxml/bands/band
  return 
    <band>
    {
      $findband/name, 
      $findband/nationality, 
      let $albums-per-band := $albumsxml/albums/album[band = $findband/name]
      for $genre in distinct-values($albums-per-band/genre)
      order by $genre
      let $albums := $albums-per-band[genre = $genre]

      return element {"albums"} {
        attribute {"genre"} {$genre},
        attribute {"active"} {string-join((xs:string(min($albums/date)), "-", xs:string(max($albums/date))), "")},
        attribute {"count"} {count($albums)},
        for $album in $albums
        return element {"album"} {
          attribute {"date"} {$album/date},
          $album/title
        }
      }
    }
    </band>
}
</bands>

In the first for loop it gets all distinct genres per band. It then uses this information and $albums is a sequence of albums for one particular band having a certain genre.

Answer 2

Here is one way to do it in XQuery 3.0:

xquery version "3.0";

let $bands := <bands>
  <band>
    <name>Metallica</name>
    <nationality>American</nationality>
  </band>
  <band>
    <name>Marilyn Manson</name>
    <nationality>American</nationality>
  </band>
</bands>

let $albums := <albums>
  <album>
    <title>Master of Puppets</title>
    <band>Metallica</band>
    <date>1986</date>
    <genre>rock</genre>
  </album>
  <album>
    <title>St. Anger</title>
    <band>Metallica</band>
    <date>2003</date>
    <genre>rock</genre>
  </album>
  <album>
    <title>The Golden Age of Grotesque</title>
    <band>Marilyn Manson</band>
    <date>2004</date>
    <genre>rock</genre>
  </album>
  <album>
    <title>Mechanical Animals</title>
    <band>Marilyn Manson</band>
    <date>1998</date>
    <genre>pop</genre>
  </album>
</albums>

return 
element { 'bands' } {
    for $findband in $bands//band
    return 
    element { 'band' } {
        $findband/name, 
        $findband/nationality, 
        for $findalbum in $albums//album
        let $genre := $findalbum/genre/text()
        where $findalbum/band = $findband/name
        group by $genre
        order by $genre
        return 
        element { 'albums' } {
            attribute { 'genre' } { $genre },
            attribute { 'active' } { fn:min($findalbum/date/text()) ||'-' || fn:max($findalbum/date/text()) }, 
            attribute { 'count' } { fn:count($findalbum) }, 
            for $album in $findalbum
            return
            element { 'album' } {
                attribute { 'date' } { $album/date/text()},
                $album/title
            }
        }
    }
}