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.stehule@gmail.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.learning@gmail.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
>