Re: how to create aggregate xml document in 8.3?

> "Matt Magoffin" <postgresql.org@msqr.us> writes:
>> 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.
>
> Something involving xmlagg in the sub-query, perhaps?  No time to
> experiment with it now.

Thanks for the first tip, anyway. I got stuck with trying this out
myself... I had ended up with

select xmlelement(
name "matchback-months",
xmlattributes(1 as "count", 'true' as "multi"),
xmlagg(ranges)) from (
    select xmlelement(name "range",
        xmlattributes(m.range, count(s.id) as "sales-conv-from-lead"),
        xmlagg(sales)) from (
            select xmlelement(name "sale",
                xmlattributes(ss.vin, ms.lead_id as "lead-id")
            ) as sales
            from mb_sale ss
            inner join mb_lead ms on ms.sale_id = ss.id
            where
                ss.sale_date >= date('2007-08-01') and ss.sale_date <= date('2007-08-30')
                and ss.sale_type = 'd'
                and ms.range = m.range
            order by ss.sale_date
        ) ssub
    ) as ranges
    from mb_sale s
    inner join mb_lead m on m.sale_id = s.id
    where
        s.sale_date >= date('2007-08-01') and s.sale_date <= date('2007-08-30')
        and s.sale_type = 'd'
    group by m.range
    order by m.range
) sub;

but this does not compile:

ERROR:  syntax error at or near "from"
LINE 20: from mb_sale s
         ^

If anyone has any suggestions, much appreciated.

-- m@