Re: grouping of query data in xml
| От | c k |
|---|---|
| Тема | Re: grouping of query data in xml |
| Дата | |
| Msg-id | CAN2Y=uMxJmTFZZBb=Cub-mL1OntCukNw6xUatnWb9un_eq5NAQ@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: grouping of query data in xml ("David Johnston" <polobo@yahoo.com>) |
| Список | pgsql-general |
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.
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@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.
В списке pgsql-general по дате отправления: