Как заселектить XML

<project id="1232">
  <user id="240" role="2" /> 
  <user id="240" role="3" /> 
  <user id="240" role="20" /> 
  <user id="241" role="1" /> 
  <user id="241" role="2" /> 
  <user id="241" role="11" /> 
  <user id="243" role="2" /> 
  <user id="243" role="15" /> 
  <user id="249" role="2" /> 
  <user id="251" role="2" /> 
  </project>
<project id="111">
  <user id="240" role="5" /> 
  <user id="240" role="6" /> 
</project>

Как заселектить чтобы получить результат такой структуры:
|project_id|user_id|role|

Типа такого, только надо добавить корневой тег:

with x as (
  select extract(value(d),'/project/@id').getNumberVal() project_id
       , extract(value(d),'/project/user').getClobVal() users
  from table(XMLSequence(XMLType(
'<projects>
  <project id="1232">
      <user id="240" role="2"/>
      <user id="240" role="3"/>
      <user id="240" role="20"/>
      <user id="241" role="1"/>
      <user id="241" role="2"/>
      <user id="241" role="11"/>
      <user id="243" role="2"/>
      <user id="243" role="15"/>
      <user id="249" role="2"/>
      <user id="251" role="2"/>
  </project>
  <project id="111">
    <user id="240" role="5"/>
    <user id="240" role="6"/>
  </project>
</projects>'
       ).extract('projects/project'))) d
)
select x.project_id
     , extract(value(d2),'/user/@id').getNumberVal() user_id
     , extract(value(d2),'/user/@role').getNumberVal() role_id
from x
   , table(XMLSequence(XMLType('<users>'||x.users||'</users>').extract('/users/user'))) d2;

Даже чуть проще, без присоединения <users>:

with x as (
  select extract(value(d),'/project/@id').getNumberVal() project_id
       , extract(value(d),'/project').getStringVal() users
  from table(XMLSequence(XMLType(
'<projects>
  <project id="1232">
      <user id="240" role="2"/>
      <user id="240" role="3"/>
      <user id="240" role="20"/>
      <user id="241" role="1"/>
      <user id="241" role="2"/>
      <user id="241" role="11"/>
      <user id="243" role="2"/>
      <user id="243" role="15"/>
      <user id="249" role="2"/>
      <user id="251" role="2"/>
  </project>
  <project id="111">
    <user id="240" role="5"/>
    <user id="240" role="6"/>
  </project>
</projects>'
       ).extract('projects/project'))) d
)
select x.project_id
     , extract(value(d2),'/user/@id').getNumberVal() user_id
     , extract(value(d2),'/user/@role').getNumberVal() role_id
from x
   , table(XMLSequence(XMLType(x.users).extract('/project/user'))) d2;

Спасибо !!! Огромное.... начинаю разбираться немного...

Всем привет. вот копаю XML и не понимаю...

если мы делаем так:

SELECT D2.EXTRACT('/user/@id').GETNUMBERVAL() USER_ID
  FROM TABLE (XMLSEQUENCE(XMLTYPE('
                    <projects>
                      <project id="1232">
                          <user id="240" role="2"/>
                          <user id="240" role="3"/>
                          <user id="240" role="20"/>
                          <user id="241" role="1"/>
                          <user id="241" role="2"/>
                          <user id="241" role="11"/>
                          <user id="243" role="2"/>
                          <user id="243" role="15"/>
                          <user id="249" role="2"/>
                          <user id="251" role="2"/>
                      </project>
                    </projects>').EXTRACT('projects/project/user'))) D2;

то результат получаем такой

240                                   
240                                   
240                                   
241                                   
241                                   
241                                   
243                                   
243                                   
249                                   
251                                   

а если в таблицу в поле XML_METADATA типа XMLTYPE поместить

                    <projects>
                      <project id="1232">
                          <user id="240" role="2"/>
                          <user id="240" role="3"/>
                          <user id="240" role="20"/>
                          <user id="241" role="1"/>
                          <user id="241" role="2"/>
                          <user id="241" role="11"/>
                          <user id="243" role="2"/>
                          <user id="243" role="15"/>
                          <user id="249" role="2"/>
                          <user id="251" role="2"/>
                      </project>
                    </projects>

и попытаться выбрать запросом

SELECT D.XML.EXTRACT('/user/@id').GETNUMBERVAL() USER_ID
  FROM (SELECT O.XML_METADATA.EXTRACT('projects/project/user') XML
          FROM MD$OBJECT O
         WHERE O.ID_OBJECT = 120) D;

то получаем

240240240241241241243243249251        

и где же таблица? почему результат выдается в одной ячейке?
Нужно что бы результат отобразился так же как и в первом случае.
Разъясните и помогите пожалуйста. Заранее благодарен.

stepa405

...

xmlsequence missed