7 Replies Latest reply on Jul 12, 2016 4:14 PM by ORA_CARE

    Error while parsing XML string : LPX-00242: invalid use of ampersand ('&') character (use &)

    ORA_CARE

      Dear Forum members,

       

      I'm getting the oracle xml parsing error if we receive the ampersand char or any special character in the xml tag value. if there is no special character the code is working fine.

       

      i'm taking out the value coming within the xml quotes and printing it in text file using UTL_FILE utility.  Coming ampersand is a possible scenarion in my case and i've to print the smae value in the file after extracting from the tag.

       

      please advise me the best way to resolve this issue.

       

      Oracle version used :  11.2.0.4.0

       

      Working code:

      SELECT x.*

           FROM (select '<f4>Hello</f4><f50>SAM </f50>' line_notes from dual)

           n,

           XMLTABLE('/root'

                      passing xmltype('<root>'||line_notes||'</root>')

                      columns f4   VARCHAR2(17) PATH 'f4',

                              f50   VARCHAR2(65) PATH 'f50'

                      ) X

       

      Not working code:

       

      SELECT x.*

           FROM (select '<f4>Hello</f4><f50>SAM &</f50>' line_notes from dual)

           n,

           XMLTABLE('/root'

                      passing xmltype('<root>'||line_notes||'</root>')

                      columns f4   VARCHAR2(17) PATH 'f4',

                              f50   VARCHAR2(65) PATH 'f50'

                      ) X

       

      Error:

      ORA-31011: XML parsing failed

      ORA-19202: Error occurred in XML processing

      LPX-00242: invalid use of ampersand ('&') character (use &amp;)

      Error at line 1

      ORA-06512: at "SYS.XMLTYPE", line 310

      ORA-06512: at line 1

      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.