SQL> with data as (
  2    select xmltype('<emp>
  3  <row>
  4  <empno>3</empno>
  5  <ename>ra</ename>
  6  </row>
  7  <row>
  8  <empno>9</empno>
  9  <ename>a</ename>
 10  </row>
 11  <row>
 12  <empno>6</empno>
 13  <ename>r</ename>
 14  </row>
 15  </emp>') xml_data
 16    from dual )
 17  select extractvalue(value(x),'/row/ename') ename
 18  from data, table(xmlsequence(extract(xml_data,'/emp/row'))) x
 19  /
ENAME
----------------------------------------------------------------
ra
a
r

3 rows selected.