Thread: grouping of query data in xml

grouping of query data in xml

From

c k

Date:

21 January 2012, 11:22:52

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>

</docs>
<docs>

</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: grouping of query data in xml

From

"David Johnston"

Date:

21 January 2012, 11:44:23

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>

</docs>
<docs>

</docs>

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: grouping of query data in xml

From

c k

Date:

21 January 2012, 11:53:15

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@yahoo.com> wrote:

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: grouping of query data in xml

From

Pavel Stehule

Date:

21 January 2012, 12:11:19

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
>

Re: grouping of query data in xml

From

c k

Date:

21 January 2012, 13:21:07

I have written a query as follows

select xmlelement(name accgroup, xmlforest(accgroupid, accgroupname, xmlforest(refaccdocid,d, c) as doc))::xml AS xmldata
from (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 10) as b

which gives output I need in really fast way. (processing 1000 rows in around 200 ms)
but when using query_to_xml with the same output is taking much higher time (processing 1000 rows in around 4000 ms) .

Can you please give me the answer for this? Why query_to_xml is taking too much time?

Regards,

CPKulkarni

On Sat, Jan 21, 2012 at 9:40 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

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
>

Re: grouping of query data in xml

From

Pavel Stehule

Date:

21 January 2012, 13:34:45

2012/1/21 c k <shreeseva.learning@gmail.com>:
> I have written a query as follows
>
> select xmlelement(name accgroup, xmlforest(accgroupid, accgroupname,
> xmlforest(refaccdocid,d, c) as doc))::xml AS xmldata
>  from (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 10) as b
>
> which gives output I need in really fast way. (processing 1000 rows in
> around 200 ms)
> but when using query_to_xml with the same output is taking much higher time
> (processing 1000 rows in around 4000 ms) .
>
> Can you please give me the answer for this? Why query_to_xml is taking too
> much time?

I don't know - I see only useles casts in your queries, but in both queries

SELECT xmlforest(....)::xml is nonsens

Regards

Pavel

>
> Regards,
>
> CPKulkarni
>
>
> On Sat, Jan 21, 2012 at 9:40 PM, Pavel Stehule <pavel.stehule@gmail.com>
> wrote:
>>
>> 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
>> >
>
>

Re: grouping of query data in xml

From

c k

Date:

21 January 2012, 14:08:10

It was not intentionally made, but was remaining due to copy-paste from other queries. It is only one occurrence of unnecessary casting. I got the idea about such a query from an oracle pl/sql book.

C P Kulkarni

On Sat, Jan 21, 2012 at 11:03 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

2012/1/21 c k <shreeseva.learning@gmail.com>:
> I have written a query as follows
>
> select xmlelement(name accgroup, xmlforest(accgroupid, accgroupname,
> xmlforest(refaccdocid,d, c) as doc))::xml AS xmldata
> from (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 10) as b
>
> which gives output I need in really fast way. (processing 1000 rows in
> around 200 ms)
> but when using query_to_xml with the same output is taking much higher time
> (processing 1000 rows in around 4000 ms) .
>
> Can you please give me the answer for this? Why query_to_xml is taking too
> much time?

I don't know - I see only useles casts in your queries, but in both queries

SELECT xmlforest(....)::xml is nonsens

Regards

Pavel

>
> Regards,
>
> CPKulkarni
>
>
> On Sat, Jan 21, 2012 at 9:40 PM, Pavel Stehule <pavel.stehule@gmail.com>
> wrote:
>>
>> 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
>> >
>
>