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'
