By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
with t (overview) as (
SELECT '<div><p>Some entities: & ' < > to be handled </p></div>' from dual UNION ALL
SELECT '<html><head><title>Test</title></head><body><p><test></p></body></html>' from dual
)
SELECT x.*
FROM t
CROSS JOIN LATERAL (
SELECT LISTAGG(value) WITHIN GROUP (ORDER BY ROWNUM) AS text
FROM XMLTABLE(
'//*'
PASSING XMLTYPE(t.overview)
COLUMNS
value CLOB PATH './text()'
)
) x
| TEXT |
|---|
| Some entities: & ' < > to be handled |
| Test<test> |