Re: grouping of query data in xml

I have written a query as follows

select xmlelement(name accgroup, xmlforest(accgroupid, accgroupname,
xmlforest(refaccdocid,d, c) as doc))::xml AS xmldata
from (select 'acc group' as accgroup, accgroups.accgroupid,
accgroups.accgroupname, 'docs' as doc, act.refaccdocid, sum(act.debit) as
d, sum(act.credit) as c from accgroups inner join accountingtransactions as
act on accgroups.accgroupid=act.accgroupid where accgroups.accgroupid
between 6050 and 6055 group by accgroups.accgroupid,
accgroups.accgroupname, act.refaccdocid limit 10) as b

which gives output I need in really fast way. (processing 1000 rows in
around 200 ms)
but when using query_to_xml with the same output is taking much higher time
(processing 1000 rows in around 4000 ms) .

Can you please give me the answer for this? Why query_to_xml is taking too
much time?

Regards,

CPKulkarni

On Sat, Jan 21, 2012 at 9:40 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>wrote:

> Hello
>
> there are others SQL/XML functions - with this function, you can
> create xml like you need
>
> http://www.postgresql.org/docs/9.1/static/functions-xml.html
>
> Regards
>
> Pavel Stehule
>
>
> 2012/1/21 c k <shreeseva(dot)learning(at)gmail(dot)com>:
> > Hi friends,
> >
> > I am working with some xml exporting functionality of our application. I
> am
> > testing query_to_xml function supported in PostgreSQL 9.1. XML output
> should
> > be
> > <accgroup>
> > <accgroupid>1</accgroupid>
> > <accgroupname>COMPANY ACCOUNT</accgroupname>
> > <docs>
> > <refaccdocid>638168</refaccdocid>
> > <debit>10000.0000</debit>
> > <credit>0.0000</credit>
> > </docs>
> > <docs>
> > <refaccdocid>638168</refaccdocid>
> > <debit>0.0000</debit>
> > <credit>10000.0000</credit>
> > </docs>
> > </accgroup>
> >
> > but the output is as follows:
> > <row>
> > <accgroup>acc group</accgroup>
> > <accgroupid>1</accgroupid>
> > <accgroupname>COMPANY ACCOUNT</accgroupname>
> > <docs>docs</docs>
> > <refaccdocid>638168</refaccdocid>
> > <debit>10000.0000</debit>
> > <credit>0.0000</credit>
> > </row>
> >
> > <row>
> > <accgroup>acc group</accgroup>
> > <accgroupid>1</accgroupid>
> > <accgroupname>COMPANY ACCOUNT</accgroupname>
> > <docs>docs</docs>
> > <refaccdocid>638168</refaccdocid>
> > <debit>0.0000</debit>
> > <credit>10000.0000</credit>
> > </row>
> >
> > How can grouping on specific columns can be done in result xml through
> using
> > query_to_xml?
> > I am expecting help from postgresql community as always.
> >
> > Thanks
> >
> > CPKulkarni
> >
>