I need to return XML data formatted data from a PostgreSQL table

Question

I am using PostreSQL 14.3. I am trying to put something similar to this as a PostgreSQL Function:

select xmlelement(name car, xmlforest(make, model))
from cars

and run it against a table like this:

table cars   make varchar(20)   model varchar(20)
    
data ('Dodge','Viper'), ('Ford','Thunderbird')

I am missing a root element and I cannot seem to add one.

I would like to see:

<cars>  
    <car><make>Dodge</make><model>Viper</model></car>  
    <car><make>Ford</make><model>Thunderbird</model></car>
</cars>

but what I get is:

<car><make>Dodge</make><model>Viper</model></car>
<car><make>Ford</make><model>Thunderbird</model></car>

Frank Heikens · Accepted Answer · 2022-05-27 08:35:25Z

1

This one works, using XMLAGG to aggregate all rows into a single document:

SELECT xmlelement(name cars, XMLAGG(x)) 
FROM (
    SELECT xmlelement(name car, xmlforest(make, model)) 
    FROM cars
) sub(x);

answered May 27, 2022 at 8:35

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1