Thread: nesting XmlAgg

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
tryingto build a document such as:<br /><br /><Catalog Name="SomeCatalog"><br />  <Tables><br />   
<TableName="Table1"><br />      <Columns><br />        <Column Name="Col001" Type="Integer" /><br
/>       <Column Name="Col002" Type="varchar(100)" /><br />       </Columns><br />    </Table><br
/>   <Table Name="Table2"><br />         <Column Name="Col001" Type="Integer" /><br />         <Column
Name="Col002"Type="varchar(100)" /><br />     </Table><br />  </Tables><br /></Catalog><br /><br
/><br/>the problem i'm encountering involves what the function "XmlAgg" claims to solve... using this query, you'll
immediatelysee my dilemna... though, i'm not able to nest XmlAgg in order to use subqueries to resolve the incorrect
partitioningat the "Table" element depth... i am working towards implementing postgres for a local non-profit, but if i
can'tget up to speed on the xml functionality, then i fear that i'll have to revert back to the more familiar sql
server2005 (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... <br /><br />thanks!<br />Isaac<br /><br />select <br />  XmlElement(name
"Catalog",<br/>    XmlElement(name "Tables",<br />      XmlAgg(XmlElement(name "Table", XmlAttributes(T.table_name as
"Name"),<br/>     XmlElement(name "Columns",<br />-- i was attempting to aggregate here as well<br />        (select
XmlElement(name"Column", C.Column_Name)) <br />    )<br />      ))<br />    )<br />  )<br />from
information_schema.tablesT<br /> inner join information_schema.columns C<br />    on T.table_name = C.table_name and
T.table_schema= C.table_schema<br />where T.table_schema = 'public'<br /> 

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