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.steh...@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.learn...@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 > > >
