grouping of query data in xml

Started by c kabout 14 years ago7 messagesgeneral

shreeseva.learning@gmail.com

about 14 years ago

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 G. Johnston

david.g.johnston@gmail.com

about 14 years ago

In reply to: c k (#1)

Re: [GENERAL] grouping of query data in xml

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>

</accgroup>

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.

c k

shreeseva.learning@gmail.com

about 14 years ago

In reply to: David G. Johnston (#2)

Re: grouping of query data in xml

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:

Show quoted text

*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.****

Pavel Stehule

pavel.stehule@gmail.com

about 14 years ago

In reply to: c k (#1)

Re: [GENERAL] grouping of query data in xml

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>:

Show quoted text

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

c k

shreeseva.learning@gmail.com

about 14 years ago

In reply to: Pavel Stehule (#4)

Re: grouping of query data in xml

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:

Show quoted text

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

Pavel Stehule

pavel.stehule@gmail.com

about 14 years ago

In reply to: c k (#5)

Re: grouping of query data in xml

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

Show quoted text

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

c k

shreeseva.learning@gmail.com

about 14 years ago

In reply to: Pavel Stehule (#6)

Re: grouping of query data in xml

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:

Show quoted text

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