如何在 XMLtable() 中允許 & 符號？

從這個問答開始，請注意，雖然這有效：

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

…這失敗了（注意在字元串中添加了“ AT&T 和 AT&T ”）：

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

帶有錯誤消息：

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.

怎麼修？

---

第一次粗略的嘗試：

replace('Hallöle AT&T and AT & T slovenĈina Hallöle slovenđina', ' & ', ' and ') 

雖然這有效（至少對於“AT＆T”，但最好在最終輸出中保留＆符號……

用作'&\2'替換字元串，反向引用第二個 ( ) 組：

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>' || regexp_replace(a.str,'(&)([^#])','&38;\2') || '</dat>' )
 columns
  str2 varchar2(4000) path '/dat'
) b

錯誤資訊清楚地告訴你使用&.

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
;

STR
-------------------------------------------------------------------------
STR2
--------------------------------------------------------------------------------
Hallöle AT&T and AT & T slovenĈina Hallöle slovenđina
Hallöle AT&T and AT & T slovenĈina Hallöle slovenđina

更新（不適用於其他特殊字元，例如>）：

with data as (
 select regexp_replace('Hallöle AT&T and AT & T slovenĈina Hallöle slovenđ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&T and AT & T slovenĈina Hallöle slovenđina
Hallöle AT&T and AT & T slovenĈina Hallöle slovenđina

引用自：https://dba.stackexchange.com/questions/268361