Re: XML output & multiple SELECT queries

[Peter Eisentraut <peter_e@xxxxxxx>]

Peter Billen wrote:
> 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>

SELECT XMLElement(name team, XMLAttributes(team.name as name), XMLElement(name 
players, XMLAgg(XMLElement(name name, player_of_team.name)))) FROM team JOIN 
player_of_team ON team.id = player_of_team.team_id GROUP BY team.name;