XML output & multiple SELECT queries

Dear PostgreSQL users,

I would like to ask a question about outputting data as XML. Say I have
two
tables:

team(integer id, text name);
player_of_team(integer id, integer team_id, text name); (team_id is FK to
team.id)

I would like to query both tables to get following example XML output:

<team name="Real Madrid">
     <players>
         <name>Garcia</name>
         <name>Robinho</name>
     </players>
</team>

Is this possible in one query? I'm thinking about something like this, but
I
haven't figured it out so far:

SELECT XMLElement(name team, XMLAttributes(name as name), SELECT
XMLElement(name
players, XMLForest(name)) FROM player_of_team WHERE team_id = t.id) FROM
team t
WHERE t.name = 'Real Madrid';

In other words, is it possible to nest multiple SELECT queries in the XML
output
functions?

Thanks in advance. Kind regards,

Peter


-- 
Sent via pgsql-general mailing list (pgsql-general@[EMAIL PROTECTED]
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general