APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Ignore special characters in XMLtable element.

Sruthi TamiriApr 27 2016 — edited Apr 28 2016

Hi fac586,

I have created a sample xmltable, could you please let me know where & how to implement special characters

Following special characters requires to handle , before sending xml messages.

Invalid XML Character	Replaced With
<	<
>	>
"	"
'	'
&	&

1. Procedure indicates calling web service call


PROCEDURE XML_P1 (PI_EMPID        IN     NUMBER,                                      
                  PI_EMPNAME      IN     VARCHAR2,                                           
                  PI_DEPTNAME     IN     VARCHAR2,                                                                                  
                  PO_EID          OUT    number,  
                  PO_STATUS       OUT    NUMBER,                                           
                  PO_ERROR        OUT    VARCHAR2   
                  )  
   IS  
      v_clob         VARCHAR2(32657);   
      v_clob_val     CLOB;  
      v_status       NUMBER;  
      v_start_time   DATE;  
      v_end_time     DATE;  
        
   BEGIN  
      v_status := 0;  
      po_error:=null;  
      apex_web_service.g_request_headers(1).name := 'Content-type';    
      apex_web_service.g_request_headers(1).value := 'Application/xml';  
    
      v_clob_val :=              
            '<PHASEI>'                   
         || '<PI_EMPID>'  
         || PI_EMPTID  
         || '</PI_EMPID>'           
         || '<PI_EMPNAME>'  
         || PI_EMPNAME  
         || '</PI_EMPNAME>'           
         || '<PI_DEPTNAME>'  
         || PI_DEPTNAME  
         || '</PI_DEPTNAME>'           
         || '</PHASEI>';  
           
           
           
      v_clob :=  
            APEX_WEB_SERVICE.make_rest_request (  
              p_url           => 'http://10.201.178.77:8080/RESTfulWebService/SPoEst3Clob',                
              p_http_method   => 'POST',  
              p_body          => v_clob_val);  
                
                
              PO_EID:=v_clob;   
               
                
  
  
      v_end_time := SYSDATE;  
   EXCEPTION  
      WHEN OTHERS  
      THEN  
         --v_status := 1;  
         PO_STATUS:=1;  
         PO_ERROR:=SUBSTR (SQLERRM, 1, 1200);  
           
   END XML_P1;

2. Procedure two indicates using XMLtable format


PROCEDURE XML_P2 (  
                                          PI_XML_DATA IN CLOB,   
                                          PO_STATUS OUT NUMBER,  
                                          PO_ERROR OUT VARCHAR2,  
                                          P57_OUTPARAM OUT CLOB)  
      IS  
          l_xml               CLOB;  
          l_start_time        DATE;  
          l_end_time          DATE;            
          PO_empid          NUMBER;  
          po_clob_out         CLOB;  
            
            
            
             
   BEGIN  
      l_start_time := SYSDATE;  
      po_status := 0;  
      l_xml := PI_XML_DATA;  
      PO_ERROR :=null;  
        
        
      FOR CUR_REC  
         IN (SELECT  
                        X1.EMPID,                         
                        X1.EMPNAME,                          
                        X1.DEPTNAME                         
     FROM XMLTABLE (  
                           'PHASEI'  
                            PASSING XMLTYPE (l_xml)  
      COLUMNS                 
               "EMPID"       NUMBER (20)         PATH 'PI_EMPID' ,                                            
               "EMPNAME"     VARCHAR2 (200 BYTE) PATH 'PI_EMPNAME' ,                                
               "DEPTNAME"    VARCHAR2 (200 BYTE) PATH 'PI_DEPTNAME' )X1)                 
               loop  
               DBMS_OUTPUT.put_line(                       
                     'EMPID='      || CUR_REC.EMPID        ||                       
                     'EMPNAME='    || CUR_REC.EMPNAME      ||                     
                     'DEPTNAME='   || CUR_REC.DEPTNAME       
                     );  
                                              
                       
                       
                       
                       
               end loop;  
                 
               l_end_time := SYSDATE;  
            EXCEPTION  
          WHEN OTHERS  
          THEN  
             po_status := 1;  
               
                
         END XML_P2;

3. Indicates block for reference


DECLARE   
  PI_XML_DATA CLOB;  
  PO_STATUS NUMBER;  
  PO_ERROR VARCHAR2(32767);  
  P57_OUTPARAM CLOB;  
  PO_EMPID VARCHAR2(32767);  
    
  
  
BEGIN   
  
  
  
  
  PI_XML_DATA :='<PHASEI>  
                          <PI_EMPID>1335713</PI_EMPID>                          
                          <PI_EMPNAME>ETL & Oil.</PI_EMPNAME>  
   <PI_DEPTNAME>Sunset & MoonSet.</PI_DEPTNAME>  
                 </PHASEI>';    
  PO_STATUS := NULL;  
  PO_ERROR := NULL;  
    
  
  
  XMl_P2 ( PI_XML_DATA, PO_STATUS, PO_ERROR, P57_OUTPARAM );  
    
  COMMIT;   
END;

Regards,

Sruthitamiri

This post has been answered by fac586 on Apr 27 2016

Jump to Answer

Locked Post

New comments cannot be posted to this locked post.

Locked on May 26 2016

Added on Apr 27 2016

#apex-discussions

21 comments

8,226 views