From: | Peter Billen <peter(at)clueless(dot)be> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | XML output & multiple SELECT queries |
Date: | 2008-06-14 09:50:39 |
Message-ID: | 20080614115039.90n2ocnylxnoss8w@webmail.clueless.be |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Ciprian Dorin Craciun | 2008-06-14 10:50:50 | Re: Backup using GiT? |
Previous Message | D Galen | 2008-06-14 07:51:54 | Re: Overloading |