[ADMIN] grouping of query data in xml

[c k]

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

[David Johnston]

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

[Pavel Stehule]

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

Post by c k
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>
<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