0

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

2
  • @mustaccio I have no control over the string ("Hallöle AT&T and AT & T sloven&#264;ina Hallöle sloven&#273;ina"); I can only parse it, and xmltable() does take care of the latter special characters, but not of the ampersand characters. Jun 2, 2020 at 13:20
  • Nowadays with UTF-8 HTLM entities are not really state-of-the-art anymore. W3.org says: It is almost always preferable to use an encoding that allows you to represent characters in their normal form, rather than using named character references or numeric character references. Using escapes can make it difficult to read and maintain source code, and can also significantly increase file size. Jun 6, 2020 at 18:18

3 Answers 3

0

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
4
  • I have no control over the string ("Hallöle AT&T and AT & T sloven&#264;ina Hallöle sloven&#273;ina"); I can only parse it, and xmltable() does take care of the latter special characters, but not of the ampersand characters. Jun 2, 2020 at 13:20
  • @nuttyaboutnatty Then just use some regexp. See my update for example. It is not complete, works on this example, but will not work if you have other special characters like &gt;. Unfortunately this is something you need to handle, your input does not follow the standard, your &s should be &amp;s in the input. Jun 2, 2020 at 13:44
  • I just posted an (incomplete) answer with a regex which works; I only need to find a way to get the ampersand in the replacement string - without being prompted for a value. Jun 2, 2020 at 13:55
  • 1
    @nuttyaboutnatty That is because & is a special character marking a substitution variable in sqlplus or SQL Developer. Use set define off to disable that feature. Jun 2, 2020 at 13:56
0

Using '&#38;\2' as replacement string, backreferencing the 2nd ( ) group:

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

I think the simplest solution is to use build-in function DBMS_XMLGEN.CONVERT:

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

Function HTF.ESCAPE_SC should also work.

1
  • That's the wrong string (cf. original question). Jun 6, 2020 at 19:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.