Starting with this Q&A, note that while this works:
with data as (
select 'Hallöle slovenĈina Hallöle slovenđina' str from dual
)
select a.str, b.str2
from data a, xmltable( '/'
passing xmltype( '<dat>' || a.str || '</dat>' )
columns
str2 varchar2(4000) path '/dat'
) b
… this fails (note the addition of "AT&T and AT & T" to the string):
with data as (
select 'Hallöle AT&T and AT & T slovenĈina Hallöle slovenđina' str from dual
)
select a.str, b.str2
from data a, xmltable( '/'
passing xmltype( '<dat>' || a.str || '</dat>' )
columns
str2 varchar2(4000) path '/dat'
) b
with error message:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00242: invalid use of ampersand ('&') character (use &)
Error at line 2
31011. 00000 - "XML parsing failed"
*Cause: XML parser returned an error while trying to parse the document.
*Action: Check if the document to be parsed is valid.
How to fix?
First crude attempt(s):
replace('Hallöle AT&T and AT & T slovenĈina Hallöle slovenđina', ' & ', ' and ')
While this works (at least for "AT & T", it would be desirable to preserve the ampersand in the final output…