How to allow for ampersand in XMLtable()? Starting with this Q&amp;A, note that while this works:

with data as (
  select 'Hallöle sloven&amp;#264;ina Hallöle sloven&amp;#273;ina' str from dual
)
select a.str, b.str2
from data a, xmltable( '/'
  passing xmltype( '&lt;dat&gt;' || a.str || '&lt;/dat&gt;' )
  columns
   str2 varchar2(4000) path '/dat'
) b


… this fails (note the addition of "AT&amp;T and AT &amp; T" to the string):

with data as (
  select 'Hallöle AT&amp;T and AT &amp; T sloven&amp;#264;ina Hallöle sloven&amp;#273;ina' str from dual
)
select a.str, b.str2
from data a, xmltable( '/'
  passing xmltype( '&lt;dat&gt;' || a.str || '&lt;/dat&gt;' )
  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 ('&amp;') character (use &amp;amp;)
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&amp;T and AT &amp; T sloven&amp;#264;ina Hallöle sloven&amp;#273;ina', ' &amp; ', ' and ') 


While this works (at least for "AT &amp; T", it would be desirable to preserve the ampersand in the final output…
The error message clearly told you to use &amp;amp;.

with data as (
  select 'Hallöle AT&amp;amp;T and AT &amp;amp; T sloven&amp;#264;ina Hallöle sloven&amp;#273;ina' str from dual
)
select a.str, b.str2
from data a, xmltable( '/'
  passing xmltype( '&lt;dat&gt;' || a.str || '&lt;/dat&gt;' )
  columns
   str2 varchar2(4000) path '/dat'
) b
;

STR
-------------------------------------------------------------------------
STR2
--------------------------------------------------------------------------------
Hallöle AT&amp;amp;T and AT &amp;amp; T sloven&amp;#264;ina Hallöle sloven&amp;#273;ina
Hallöle AT&amp;T and AT &amp; T slovenĈina Hallöle slovenđina


Update (will not work for other special characters such as &amp;gt;):

with data as (
  select regexp_replace('Hallöle AT&amp;T and AT &amp; T sloven&amp;#264;ina Hallöle sloven&amp;#273;ina', '(&amp;)([^#])', '\1amp;\2') str from dual
)
select a.str, b.str2
from data a, xmltable( '/'
  passing xmltype( '&lt;dat&gt;' || a.str || '&lt;/dat&gt;' )
  columns
   str2 varchar2(4000) path '/dat'
) b
;

STR
-------------------------------------------------------------------------
STR2
--------------------------------------------------------------------------------
Hallöle AT&amp;amp;T and AT &amp;amp; T sloven&amp;#264;ina Hallöle sloven&amp;#273;ina
Hallöle AT&amp;T and AT &amp; T slovenĈina Hallöle slovenđina