Databases
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
User Name:
Password:
Remember me
Go Back   Web Development Archives Mailing Lists Databases

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Display Modes
 
Unread Web Development Archives Sponsor:
  #1  
Old July 2nd, 2008, 09:31 PM
Isaac Dover
Guest
Dev Archives Newbie (0 - 499 posts)
 
Posts: n/a  
Time spent in forums:
Reputation Power:
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 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'

Reply With Quote
Reply

Viewing: Web Development Archives Mailing Lists Databases > nesting XmlAgg


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are Off
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 6 hosted by Hostway
Stay green...Green IT