List all actors in a movie by Xquery



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>

No comments:

Post a Comment