How to Generate XML and call a query on it?

I have a table called RegionDetail with the following columns in it:

RegionNum char
RegionName char
People xml

The People column is an xml column. The first thing I need to do a query on this table that will return the result as xml. For example to do the same thing in SQL Serer 2005, I will do a query such as this:

SELECT * FROM RegionDetail as Region FOR XML AUTO, ELEMENTS, TYPE, ROOT('Regions')

This query will return me the xml in the desired format:

<Regions>

  <Region>

<RegionNum> ... </RegionNum>
<RegionName> ... </RegionName>
<People> ... </People>

  </Region>

  <Region>

<RegionNum> ... </RegionNum>
<RegionName> ... </RegionName>
<People> ... </People>

  </Region>

...

</Regions>

The next thing would be to run an XQuery on this. Again in SQL Server 2005, I will wrap the earlier SELECT in another SELECT, like this:

SELECT ( SELECT * FROM RegionDetail as Region FOR XML AUTO, ELEMENTS, TYPE, ROOT('Regions') ).query('\\Region')

My question is, how do i do the exact same using PL/SQL in Oracle (10g and above)?

ps. If there is a more appropriate place for this post, do tell. Received on Fri Jul 15 2005 - 04:24:22 CDT