mercredi 15 juin 2016

Creating an XML document through SQL, but I get syntax error

I have a small query to make an XML document:

Select XMLELEMENT(NAME movie,
XMLFOREST(Movie.name as title, year as year, rating as rating, plot_outline as plot, 
XMLELEMENT(NAME actor, 'actor',
XMLAGG(
XMLELEMENT(NAME role, Acts.role )
),
XMLAGG(
XMLELEMENT(NAME person, Person.name))   
)))
as result from Movie, Acts, Person
where Movie.mid = Acts.mid
and Acts.pid = Person.pid

But I get this error:

ERROR:  unnamed XML element value must be a column reference
LINE 3:    XMLELEMENT(NAME actor, 'actor',
           ^

This was supposed to be my expected result in XML:

<movie>
<title>Godfather, The</title>
<year>1972</year>
<rating>9.0</rating>
<plot>A Mafia boss' son, previously uninvolved in the business, takes over when his father is critically wounded in a mob hit.</plot>
<actor>
<role>Don Vito Corleone</role>
<person>Robert De Niro</person>
</actor>
<actor>
<role>Someone else</role>
<person>Someone else</person>
</actor>

</movie>

How do I get rid of this error message?

Aucun commentaire:

Enregistrer un commentaire