Am Donnerstag, 3. Juli 2008 schrieb Isaac Dover:
> 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'
Try this:
select XmlElement(name "Catalog", XmlElement(name "Tables", XmlAgg(XmlElement(name "Table",
XmlAttributes(T.table_nameas "Name"), XmlElement(name "Columns", (select XmlAgg(XmlElement(name "Column",
C.Column_Name))from
information_schema.columns C where T.table_name = C.table_name and
T.table_schema =
C.table_schema)
) )) ) )
from information_schema.tables T
where T.table_schema = 'public';