Re: nesting XmlAgg

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_name as "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';