nesting XmlAgg

From: "Isaac Dover" <isaacdover(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: nesting XmlAgg
Date: 2008-07-03 02:08:06
Message-ID: b560e3300807021908v17a2be1avf49775c6064aa787@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

hi all, i'm coming from the MS SQLXML world and attempting to learn SQL/XML,
but i'm having some trouble... my first project, which i thought would be
simple, is to create an xml model using the information schema views... i'm
simply trying to build a document such as:

<Catalog Name="SomeCatalog">
<Tables>
<Table Name="Table1">
<Columns>
<Column Name="Col001" Type="Integer" />
<Column Name="Col002" Type="varchar(100)" />
</Columns>
</Table>
<Table Name="Table2">
<Column Name="Col001" Type="Integer" />
<Column Name="Col002" Type="varchar(100)" />
</Table>
</Tables>
</Catalog>

the problem i'm encountering involves what the function "XmlAgg" claims to
solve... using this query, you'll immediately see my dilemna... though, i'm
not able to nest XmlAgg in order to use subqueries to resolve the incorrect
partitioning at the "Table" element depth... i am working towards
implementing postgres for a local non-profit, but if i can't get up to speed
on the xml functionality, then i fear that i'll have to revert back to the
more familiar sql server 2005 (please don't make me! :)... seeing PS's other
numerous features, i can't imagine that this is an impossibility, i just
need to right nudge...

thanks!
Isaac

select
XmlElement(name "Catalog",
XmlElement(name "Tables",
XmlAgg(XmlElement(name "Table", XmlAttributes(T.table_name as "Name"),
XmlElement(name "Columns",
-- i was attempting to aggregate here as well
(select XmlElement(name "Column", C.Column_Name))
)
))
)
)
from information_schema.tables T
inner join information_schema.columns C
on T.table_name = C.table_name and T.table_schema = C.table_schema
where T.table_schema = 'public'

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Peter Eisentraut 2008-07-03 13:15:39 Re: nesting XmlAgg
Previous Message Dhanushka Samarakoon 2008-07-02 17:12:37 Re: Need a sample Postgre SQL script