Re: how to create aggregate xml document in 8.3?

> "Matt Magoffin" <postgresql(dot)org(at)msqr(dot)us> writes:
>> Hello, I'm trying to write a query to return an XML document like
>> <root foo="bar">
>> <range range="x" count="123">
>> <range range="y" count="345">
>> ...
>> </root>
>
> Something like this:
>
> regression=# select xmlelement(name root, xmlagg(x)) from
> regression-# (select xmlelement(name range, xmlattributes(string4,
> count(*) as count)) as x from tenk1 group by string4) ss;
> xmlelement
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
> <root><range string4="OOOOxx" count="2500"/><range string4="AAAAxx"
> count="2500"/><range string4="HHHHxx" count="2500"/><range
> string4="VVVVxx" count="2500"/></root>
> (1 row)
>
> You need a subquery because your setup requires two levels of
> aggregation: one to make the grouped counts, and then another one
> for the xmlagg() (which is basically just text concatenation).

Thanks very much, that helps. Now I'm wondering if it's also possible to
then fill in another nested element level in the XML output, from the rows
that are aggregated into the <range> count. So from your example,
something like

<root>
<range string4="0000xx" count="2500">
<string4 foo="bar1"/>
<string4 foo="bar2"/>
</range>
<range string4="AAAAxx" count="2500">
<string4 foo="bar3"/>
....
</range>
....
</root>

Any thoughts on this?

-- m@