select e.first, e.last, p.phone  
from XMLTABLE('/employee'   
              PASSING XMLTYPE('  
                                  <employee>  
                                      <first>Илья</first>  
                                      <last>Муромец</last>  
                                      <office>хижина</office>  
                                      <phone>02</phone>  
                                      <phone>911</phone>  
                                  </employee>  
                              ')     
              COLUMNS  
                      first     VARCHAR2(25) PATH 'first',  
                      last      VARCHAR2(25) PATH 'last',  
                      phone_xml XMLTYPE      PATH 'phone'
                      ) AS e,  
     XMLTable('/phone'  
              PASSING e.phone_xml  
              COLUMNS  
                      phone     VARCHAR2(12) PATH '.') AS p;