Xquery FLWOR with group by

Question

I want to group all movies by genre and then list all movie titles in this genre.

My XML movie database looks as following:

<movies>
  <movie>
    <title>A History of Violence</title>
    <year>2005</year>
    <country>USA</country>
    <genre>Crime</genre>
    <summary>Tom Stall, a humble family man and owner of a 
    popular neighborhood restaurant, lives a quiet but 
    fulfilling existence in the Midwest. One night Tom 
    foils a crime at his place of business and, to his 
    chagrin, is plastered all over the news for his 
    heroics. Following this, mysterious people follow 
    the Stalls' every move, concerning Tom more than 
    anyone else. As this situation is confronted, more 
    lurks out over where all these occurrences have 
    stemmed from compromising his marriage, family 
    relationship and the main characters' former 
    relations in the process.</summary>
 <director>     
        <last_name>Cronenberg</last_name>
        <first_name>David</first_name>
        <birth_date>1943</birth_date>
</director> 
<actor>
        <first_name>Vigo</first_name>
        <last_name>Mortensen</last_name>
        <birth_date>1958</birth_date>
        <role>Tom Stall</role>
</actor>
<actor>
        <first_name>Maria</first_name>
        <last_name>Bello</last_name>
        <birth_date>1967</birth_date>
        <role>Eddie Stall</role>
</actor>
<actor>
        <first_name>Ed</first_name>
        <last_name>Harris</last_name>
        <birth_date>1950</birth_date>
        <role>Carl Fogarty</role>
</actor>
<actor>
        <first_name>William</first_name>
        <last_name>Hurt</last_name>
        <birth_date>1950</birth_date>
        <role>Richie Cusack</role>
</actor>
 </movie>

here is my expression:

xquery version "3.0";

let $movie := collection ('/db/Movie/data')/movies/movie

return

<html>
<head>

     </head>
     <body>
        <h1>Movies grouped by genre:</h1>

        <ol>{
              for $m in $movie
              let $g := $m/genre
              let $t := distinct-values($m/title/text())
              group by $g 
  return
                <li>{$g}  <p> <ol>Title: {$t}</ol> </p></li>



        }</ol>
   </body>
</html>

but the result will give me all titles in one row but I want them also als List points seperated.

This is the actual output:

<li>
<genre>Crime</genre>
<p>
<ol>Title: A History of Violence Heat Match Point</ol>
</p>
</li>
<li>

Should look like this:

<li>
<genre>Crime</genre>
<p>
<ol>Title: A History of Violence 
           Heat 
           Match Point
</ol>
</p>
</li>
<li>

How do I need to adjust the query?

Thanks in advance. Greets

Please always provide example input if your code consumes some. Very good questions also provide actual and expected output to further clarify the issue. — Jens Erat
Thanks for the hind. I edited the question and hope it clarified it. — user3181473
Apart it's better to provide at least two elements to allow actual aggregation, this now is a well-posed question. — Jens Erat

Jens Erat Jens Erat · Accepted Answer · 2014-01-11T18:48:27

Just add another loop inside. I did reformat and renamed some variables for being more descriptive. Generally, do not use text() if there's a really good reason to do so, most of the time it's better to use data() instead which aggregates all text nodes inside an element.

xquery version "3.0";

let $movies := collection ('/db/Movie/data')/movies/movie
return
  <html>
    <head></head>
    <body>
      <h1>Movies grouped by genre:</h1>
      <ol>{
        for $movie in $movies
        let $genre := $movie/genre
        group by $genre 
        let $titles := distinct-values($movie/title/data())
        return
          <li>
            <h2>{$genre} Titles</h2>
            <ol>{
              for $title in $titles
              return <li>{$title}</li>
            }</ol>
          </li>
        }</ol>
     </body>
   </html>

You could use an implicit loop using an element constructor as axis step, but this would require to remove the distinct-values call (do you really need it?) I just repeated the $movie loop:

for $movie in $movies
let $genre := $movie/genre
group by $genre 
return
  <li>
    <h2>{$genre} Titles</h2>
    <ol>{ $movie/title/element li { data() } }</ol>
  </li>

By the way, HTML does not allow lists inside paragraphs. It is well-formed XML anyway, but not valid HTML. I fixed that, too.

Xquery FLWOR with group by

1 Answers