with dummy as ( 
  select xmltype( 
  '<Employees> 
    <Employee> 
        <firstname>A</firstname> 
        <phones> 
          <phone><value>test1</value></phone> 
          <phone><value>test2</value></phone> 
        </phones> 
    </Employee> 
    <Employee> 
        <firstname>B</firstname> 
        <phones> 
          <phone></phone> 
        </phones> 
    </Employee> 
    <Employee> 
        <firstname>C</firstname> 
    </Employee> 
</Employees>' 
  ) as data from dual 
) 
select t.name, p.phone 
from dummy, 
xmltable( 
  'Employees/Employee' passing dummy.data 
  columns 
    name varchar2(20 char) path 'firstname' 
    , phone xmltype path 'phones/phone' 
    default xmltype( 
    '<phones> 
          <phone></phone> 
        </phones>' 
    ) 
) t, 
xmltable( 
  '/phone' passing t.phone 
  columns 
   phone varchar2(10 char) path 'value' 
) p

with dummy as ( 
  select xmltype( 
  '<Employees> 
    <Employee> 
        <firstname>A</firstname> 
        <phones> 
          <phone><value>test1</value></phone> 
          <phone><value>test2</value></phone> 
        </phones> 
    </Employee> 
    <Employee> 
        <firstname>B</firstname> 
        <phones> 
          <phone></phone> 
        </phones> 
    </Employee> 
    <Employee> 
        <firstname>C</firstname> 
    </Employee> 
</Employees>' 
  ) as data from dual 
) 
select t.name, p.phone 
from dummy, 
xmltable( 
  'Employees/Employee' passing dummy.data 
  columns 
    name varchar2(20 char) path 'firstname' 
    , phone xmltype path 'phones/phone' 
) t, 
xmltable( 
  '/phone' passing t.phone 
  columns 
   phone varchar2(10 char) path 'value' 
) p