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>
I started with
select xmlelement(name range, xmlattributes(m.range, count(s.id) as "count"))
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' -- direct sale
group by m.range
order by m.range;
xmlelement
------------------------------------
<range range="0-30" count="215"/>
<range range="31-60" count="202"/>
<range range="61-90" count="64"/>
<range range="91-120" count="22"/>
(4 rows)
which returns 4 individual rows as shown, but I can't figure out how to
correctly produce this with a root element and the <range> elements nested
under that.
I tried a variety of ways, including
select xmlelement(name "matchback-months",
xmlattributes('bar' as "foo"),
xmlagg(
xmlelement(name "range",
xmlattributes(m.range, count(s.id) as "count")
)
)
)
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;
which returns an error "aggregate function calls cannot be nested".
Is this type of output possible in 8.3?
-- m@
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
