SQL> with
  2    data as (
  3      select
  4  '<book>
  5  <author>Mr. Dunno</author>
  6  <year>2000</year>
  7  <price>19.99</price>
  8  </book>' val
  9      from dual
 10    )
 11  select cast(extractvalue(value(x), '//author') as varchar2(30)) author,
 12         to_number(extractvalue(value(x), '//year')) year,
 13         to_number(extractvalue(value(x), '//price')) price
 14  from data,
 15       table(xmlsequence(extract(xmltype(val), '//book'))) x
 16  /
AUTHOR                               YEAR      PRICE
------------------------------ ---------- ----------
Mr. Dunno                            2000      19.99

1 row selected.

SQL> with
  2    data as (
  3      select
  4  '<book>
  5  <author>Mr. Dunno</author>
  6  <year>2000</year>
  7  <price>19.99</price>
  8  </book>' val
  9      from dual
 10    )
 11  select x.author, x.year, x.price
 12  from data,
 13       xmltable('/book'
 14                passing xmltype (data.val)
 15                columns
 16                  "AUTHOR" varchar2(30) path '/book/author',
 17                  "YEAR"   number       path '/book/year',
 18                  "PRICE"  number       path '/book/price'
 19       ) x
 20  /
AUTHOR                               YEAR      PRICE
------------------------------ ---------- ----------
Mr. Dunno                            2000      19.99

1 row selected.