Sorry for double list posting. The query is follows. I have included a where clause to limit the rows.
select query_to_xml($$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 2$$, false, false, '')::xml AS xmldata I want to generate a xml format for multiple documents in our application where many document types share some common properties (usually stored in a single docs table) and few other properties which are specific for that document type only. The above query is not exact as I need for creating documents, but it is an example I am testing the xml functions of PostgreSQL. On Sat, Jan 21, 2012 at 9:13 PM, David Johnston <pol...@yahoo.com> wrote: > *From:* pgsql-general-ow...@postgresql.org [mailto: > pgsql-general-ow...@postgresql.org] *On Behalf Of *c k > *Sent:* Saturday, January 21, 2012 10:23 AM > *To:* pgsql-admin; pgsql-general@postgresql.org > *Subject:* [GENERAL] grouping of query data in xml**** > > ** ** > > 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**** > > ** ** > > Maybe provide the query you are trying to execute…?**** > > Also, you should not post to multiple lists. This is NOT an > administration issue so you should not have included pgsql-admin.**** > > Given that tables/query-results are two-dimensional why is it that you > expect <docs> to have child elements? Are you claiming the output is wrong > or simply that you would like a different output than what you are being > given?**** > > David J.**** >
