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_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';