List all actors in a movie by Xquery

Question

I want to list all actors for each movie I got in my eXist-db/XML-database. I've created a movies.xml and an actors.xml. (Content is pasted at the end.)

My Xquery so far is as below, and the result is listing an actor and a movie together, with the correct "join", but I want to list a movie, and then all the actors in it. I've written how I want it, but I'm not sure if it's even a good output. It just seems like the most natural as I'm used to MySQL.

for $movie in doc('db/movies/movies.xml')//movie, 
$actor in doc('db/movies/actors.xml')//actor

where $actor/@id = $movie/actors/actor
return <movact>
    {$actor//name}
    {$movie//title}
    </movact>

My result so far

<movact>
    <name>Dan</name>
    <title>Harry Putter</title>
</movact>
<movact>
    <name>Dan</name>
    <title>Star Cars</title>
</movact>
<movact>
    <name>John</name>
    <title>Star Cars</title>
</movact>
<movact>
    <name>Mary</name>
    <title>Star Cars</title>
</movact>

How I want it

<movact>
    <title>Harry Putter</title>
    <name>Dan</name>
</movact>
<movact>
    <title>Star Cars</title>
    <!-- Should the <name>s be surrounded by an <actor>-tag? -->
    <name>Dan</name>
    <name>John</name>
    <name>Mary</name>
</movact>

XML-content

Beware of bad structure.

/* movies.xml */
<?xml version="1.0"?>
<movies>
    <movie id="1">
        <title>Harry Putter</title>
        <year>2005</year>
        <actors>
            <actor>1</actor>
        </actors>
    </movie>
    <movie id="2">
        <title>Star Cars</title>
        <year>1998</year>
        <actors>
            <actor>1</actor>
            <actor>2</actor>
            <actor>3</actor>
        </actors>
    </movie>
</movies>

/* actors.xml */
<?xml version="1.0"?>
<actors>
    <actor id="1">
        <name>Dan</name>
        <gender>Male</gender>
    </actor>
    <actor id="2">
        <name>John</name>
        <gender>Male</gender>
    </actor>
    <actor id="3">
        <name>Mary</name>
        <gender>Female</gender>
    </actor>
</actors>

Answer 1

Basically you want to replace the actor ID by the actor name. So simply do a for loop for each ID and return the corresponding actor name. The following should do what you want.

for $movie in doc('db/movies/movies.xml')//movie
return <movact>
  {$movie/title}
  {
    for $id in $movie/actors/actor
    let $actor := doc('db/movies/actors.xml')//actor[@id = $id]
    return $actor/name
  }
</movact>

Regarding some of your comments in your question: The given XML is not bad in any way. In fact, it is quite common to store some ID instead of a complete element.

Additionally, we can't advice you whether or not you want an actor element around your names as we don't know your application or what you are doing. Also, it is mostly a matter of personal taste, although I would prefer a grouping element around the names. Both solutions are possible.