1 Reply Latest reply: Jun 20, 2012 9:51 AM by Jason_(A_Non) RSS

    Where to place CDATA?

    944530
      "SELECT xmltype(RCATT).extract('/JASON/RCA/SUB_PROBLEM_TYPE').getStringVal() as SPT,
      xmltype(RCATT).extract('/JASON/RCA/SUB_ROOT_CAUSE').getStringVal() as SRC,
      xmltype(RCATTT).extract('/JASON/RCA/ELABORATION').getStringVal() as ELA,
      xmltype(RCATT).extract('/JASON/RCA/PREVENTION').getStringVal() as PRE
      FROM table"                    


      some explanation:
      the above select statement is to select the child node inside the xml content.But the child note contains some special character "<>" that illegally in xml.

      <JASON>
      <RCA>
      <SUB_PROBLEM_TYPE>
      For <FS_PLS_WAIT>, it is appeared in both valid and invalid password entered case but in requirement, only valid case has stated the display of <FS_PLS_WAIT>. This is the legacy radio behavior.
      </SUB_PROBLEM_TYPE>
      </RCA>
      </JASOn>

      The result will generate the following issue.

      ORA-31011: XML parsing failed ORA-19202: Error occurred in XML processing LPX-00225: end-element tag "ELABORATION" does not match start-element tag "FS_PLS_WAIT" Error at line 10 ORA-06512: at "SYS.XMLTYPE", line 301 ORA-06512: at line 1

      I wish to know where can i put CDATA in order to bypass the checking for the special character or any other solution?
        • 1. Re: Where to place CDATA?
          Jason_(A_Non)
          Is this question still outstanding? I ask because you show CDATA usage in {thread:id=2404573}. If it is answered, please mark this thread as so.

          If still outstanding, please provide your version of Oracle
          select * from v$version;
          as there may be a better way to write the query but it depends upon your version.