pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedgrouping of query data in xml
7+ messages / 3 participants
[nested] [flat]
* grouping of query data in xml
@ 2012-01-21 15:22 c k <shreeseva.learning@gmail.com>
0 siblings, 2 replies; 7+ messages in thread
From: c k @ 2012-01-21 15:22 UTC (permalink / raw)
To: pgsql-admin; pgsql-general@postgresql.org
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [GENERAL] grouping of query data in xml
@ 2012-01-21 15:43 David Johnston <polobo@yahoo.com>
parent: c k <shreeseva.learning@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: David Johnston @ 2012-01-21 15:43 UTC (permalink / raw)
To: 'c k' <shreeseva.learning@gmail.com>; pgsql-admin; pgsql-general@postgresql.org
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: grouping of query data in xml
@ 2012-01-21 15:52 c k <shreeseva.learning@gmail.com>
parent: David Johnston <polobo@yahoo.com>
0 siblings, 0 replies; 7+ messages in thread
From: c k @ 2012-01-21 15:52 UTC (permalink / raw)
To: David Johnston <polobo@yahoo.com>; +Cc: pgsql-general@postgresql.org
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.****
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [GENERAL] grouping of query data in xml
@ 2012-01-21 16:10 Pavel Stehule <pavel.stehule@gmail.com>
parent: c k <shreeseva.learning@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Pavel Stehule @ 2012-01-21 16:10 UTC (permalink / raw)
To: c k <shreeseva.learning@gmail.com>; +Cc: pgsql-admin; pgsql-general@postgresql.org
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
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: grouping of query data in xml
@ 2012-01-21 17:20 c k <shreeseva.learning@gmail.com>
parent: Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: c k @ 2012-01-21 17:20 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: pgsql-general@postgresql.org
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
> >
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: grouping of query data in xml
@ 2012-01-21 17:33 Pavel Stehule <pavel.stehule@gmail.com>
parent: c k <shreeseva.learning@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Pavel Stehule @ 2012-01-21 17:33 UTC (permalink / raw)
To: c k <shreeseva.learning@gmail.com>; +Cc: pgsql-general@postgresql.org
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
>> >
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: grouping of query data in xml
@ 2012-01-21 18:07 c k <shreeseva.learning@gmail.com>
parent: Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: c k @ 2012-01-21 18:07 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: pgsql-general@postgresql.org
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
> >> >
> >
> >
>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2012-01-21 18:07 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-01-21 15:22 grouping of query data in xml c k <shreeseva.learning@gmail.com>
2012-01-21 15:43 ` David Johnston <polobo@yahoo.com>
2012-01-21 15:52 ` c k <shreeseva.learning@gmail.com>
2012-01-21 16:10 ` Pavel Stehule <pavel.stehule@gmail.com>
2012-01-21 17:20 ` c k <shreeseva.learning@gmail.com>
2012-01-21 17:33 ` Pavel Stehule <pavel.stehule@gmail.com>
2012-01-21 18:07 ` c k <shreeseva.learning@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox