Re: grouping of query data in xml

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 <polobo(at)yahoo(dot)com> wrote:

> *From:* pgsql-general-owner(at)postgresql(dot)org [mailto:
> pgsql-general-owner(at)postgresql(dot)org] *On Behalf Of *c k
> *Sent:* Saturday, January 21, 2012 10:23 AM
> *To:* pgsql-admin; pgsql-general(at)postgresql(dot)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.****
>