Could Not Get Columns with XMLTABLE in Oracle PL/SQL

Question

I try to get values from columns of a XML file.

I receive XML file which in my function is assigned to c_xml but for the purposes of this question is represented in the variable c_xml in the code below.

The problem is that I have not anything printed in DBMS_OUTPUT. PUT_LINE, so I could not get any values from XML file and I could not go further with my developing.

It would be great if somebody could help to understand where is the problem with extracting values from this XML. Thanks for your time :) The code is written on Oracle PL/SQL and 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;

Answer 1

Default namespace has to be included in the declaration.

XMLNamespaces('http://schemas.xmlsoap.org/soap/envelope/' AS "env"                             
            , 'http://curr_state_egn/CURR_STATE_EGNServiceService' AS "srvc"
            , default 'http://curr_state_egn/CURR_STATE_EGNService')

This statment xmlns="http://curr_state_egn/CURR_STATE_EGNService" changes defult namespaces.