Обсуждение: grouping of query data in xml

Поиск
Список
Период
Сортировка

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

Re: [GENERAL] grouping of query data in xml

От
"David Johnston"
Дата:

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.

Re: [GENERAL] grouping of query data in xml

От
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


2012/1/21 c k <shreeseva.learning@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
>