Link to home
Start Free TrialLog in
Avatar of Maria Hristova
Maria Hristova

asked on

Problem with XMLTABLE in PL/SQL Oracle 12c

Resp_7812.xml
I try to get columns from a XML file. I have attached it in here.
The code is as follows:

declare
c_xml     xmltype;
begin
      c_xml := xmltype('<?xml version=''1.0'' encoding=''utf-8''?>
<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <env:Header/>
  <env:Body>
    <srvc:returnActStateByEgnResponse xmlns="http://curr_state_egn/CURR_STATE_EGNService" xmlns:srvc="http://curr_state_egn/CURR_STATE_EGNServiceService">
      <srvc:result>
        <consents_tblType>
          <item>
            <req_id>112</req_id>
            <purpose_code>CC0100</purpose_code>
            <consent_state>0</consent_state>
          </item>
          <item>
            <req_id>112</req_id>
            <purpose_code>CC0200</purpose_code>
            <consent_state>1</consent_state>
          </item>
          <item>
            <req_id>112</req_id>
            <purpose_code>CC0300</purpose_code>
            <consent_state>0</consent_state>
          </item>
        </consents_tblType>
      </srvc:result>
    </srvc:returnActStateByEgnResponse>
  </env:Body>
</env:Envelope>');

      FOR consents_tblTypes IN
        ( SELECT
              p_req_id            
              , p_purpose_code    
              , p_consent_state    
          FROM  xmltable(
                    XMLNamespaces(
                                      'http://schemas.xmlsoap.org/soap/envelope/'               AS "env"
                                    --, 'http://www.w3.org/2001/XMLSchema-instance'               AS "xsi"
                                    , 'http://curr_state_egn/CURR_STATE_EGNServiceService'      AS "srvc"
                                    ),
                                      '/env:Envelope/env:Body/srvc:returnActStateByEgnResponse/srvc:result/consents_tblType/item'
                                PASSING  c_xml
                         COLUMNS                        
                          p_req_id           NUMBER  PATH 'req_id' --/text()
                          , p_purpose_code     VARCHAR2(20) PATH 'purpose_code' --/text()
                          , p_consent_state    NUMBER PATH 'consent_state' --/text()
                    )
        )
        LOOP        
            DBMS_OUTPUT.put_line('p_req_id = ' || to_char(consents_tblTypes.p_req_id))  ;
            DBMS_OUTPUT.put_line('p_purpose_code = ' || consents_tblTypes.p_purpose_code)  ;
            DBMS_OUTPUT.put_line('p_consent_state = ' || to_char(consents_tblTypes.p_consent_state))  ;
        END LOOP;  
end;

To c_xml variable is assigned XML from the file in the real code. But in the above example I assigned the XML directly to c_xml.
So the problem is that I could not get anything from the code.
Nothing is printed as DBMS_OUTPUT.
Nothing is got as column.
Does anybody know where is the problem?
Avatar of Helena Marková
Helena Marková
Flag of Slovakia image

I have tried something and here it is (I am just a beginner in this XML area):
declare
 c_xml     xmltype;
 begin
       c_xml := xmltype('<?xml version=''1.0'' encoding=''utf-8''?>
 <env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <env:Body>
     <srvc:returnActStateByEgnResponse xmlns:srvc="http://curr_state_egn/CURR_STATE_EGNServiceService">
       <srvc:result>
         <consents_tblType>
           <item>
             <req_id>112</req_id>
             <purpose_code>CC0100</purpose_code>
             <consent_state>0</consent_state>
           </item>
           <item>
             <req_id>112</req_id>
             <purpose_code>CC0200</purpose_code>
             <consent_state>1</consent_state>
           </item>
           <item>
             <req_id>112</req_id>
             <purpose_code>CC0300</purpose_code>
             <consent_state>0</consent_state>
           </item>
         </consents_tblType>
       </srvc:result>
     </srvc:returnActStateByEgnResponse>
   </env:Body>
 </env:Envelope>');

       FOR consents_tblTypes IN
         ( SELECT
               p_req_id            
               , p_purpose_code    
               , p_consent_state    
           FROM  xmltable(
                     XMLNamespaces(
                                       'http://schemas.xmlsoap.org/soap/envelope/'               AS "env"
                                     , 'http://curr_state_egn/CURR_STATE_EGNServiceService'      AS "srvc"
                                     ),
                                       '/env:Envelope/env:Body/srvc:returnActStateByEgnResponse/srvc:result/consents_tblType/item'
                                 PASSING  c_xml
                          COLUMNS                        
                           p_req_id           NUMBER  PATH 'req_id' --/text()
                           , p_purpose_code     VARCHAR2(20) PATH 'purpose_code' --/text()
                           , p_consent_state    NUMBER PATH 'consent_state' --/text()
                     )
         )
         LOOP        
             DBMS_OUTPUT.put_line('p_req_id = ' || to_char(consents_tblTypes.p_req_id))  ;
             DBMS_OUTPUT.put_line('p_purpose_code = ' || consents_tblTypes.p_purpose_code)  ;
             DBMS_OUTPUT.put_line('p_consent_state = ' || to_char(consents_tblTypes.p_consent_state))  ;
         END LOOP;  
 end;
ASKER CERTIFIED SOLUTION
Avatar of Maria Hristova
Maria Hristova

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
So you can accept it as solution to this question :).
Avatar of Maria Hristova
Maria Hristova

ASKER

I have received useful answer from my colleague and it works!