CREATE TABLE Foo (
  DATA VARCHAR2(4000) NOT NULL ENABLE
)

<foo>
  <a>some</a>
  <b>another</b>
  <c>some else<c>
</foo>

select xmltype(DATA).extract('/foo/*').getRootElement() as MY_TAGS form Foo;

a
b
c

(null)
(null)
(null)

select * from xmltable('foo/*'  passing xmltype('<foo>
  <a>some</a>
  <b>another</b>
  <c>some else</c>
</foo>')
columns
tag_name varchar2(100) path 'name()',
tag_value varchar2(100) path 'text()'
)