Oracle XMLTable – How to Allow Ampersand

oracleoracle-11g-r2xml

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&#264;ina Hallöle sloven&#273;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 &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&T and AT & T sloven&#264;ina Hallöle sloven&#273;ina', ' & ', ' and ')

While this works (at least for "AT & T", it would be desirable to preserve the ampersand in the final output…

Best Answer

The error message clearly told you to use &amp;.

with data as (
  select 'Hallöle AT&amp;T and AT &amp; T sloven&#264;ina Hallöle sloven&#273;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
;

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

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

with data as (
  select regexp_replace('Hallöle AT&T and AT & T sloven&#264;ina Hallöle sloven&#273;ina', '(&)([^#])', '\1amp;\2') 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
;

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