Transform Well (Table) Formatted XML into Table in Oracle

Let we have table-like xml with custom namespace and we want to transform it to the table (resultset)
to be used in select query.

Let we have function getXML() that returns the following XMLType value:

      <datas1:employmenttypeslist xmlns:datas1="http://mynamespace/">
         <datas1:employmenttypes>
            <datas1:id>676</datas1:id>
            <datas1:code>NOT_WORK</datas1:code>
            <datas1:text>Not working</datas1:text>
         </datas1:employmenttypes>
         <datas1:employmenttypes>
            <datas1:id>677</datas1:id>
            <datas1:code>OWN_BUSINESS</datas1:code>
            <datas1:text>Own business</datas1:text>
         </datas1:employmenttypes>
         <datas1:employmenttypes>
            <datas1:id>678</datas1:id>
            <datas1:code>PENSIONER</datas1:code>
            <datas1:text>Pensioner</datas1:text>
         </datas1:employmenttypes>
      </datas1:employmenttypeslist>

So, to transform the XMLType into a table we can use several methods.

Method #1

select 
extractValue(xmlt.COLUMN_VALUE,'*/p:id','xmlns:p="http://mynamespace/"') as id,
extractValue(xmlt.COLUMN_VALUE,'*/p:code','xmlns:p="http://mynamespace/"') as code,
extractValue(xmlt.COLUMN_VALUE,'*/p:text','xmlns:p="http://mynamespace/"') as text

from

TABLE(XMLSEQUENCE(

EXTRACT(
(
    select getXML() from dual
),
'/*/*'
)

)) xmlt

Method #2

select *
FROM XMLTABLE(XMLNAMESPACES ('http://mynamespace/' AS "p"),'/*/*'
         PASSING 
            getXML()
         COLUMNS
            --describe columns and path to them:
            id varchar2(10)    PATH '/*/p:id',
            code varchar2(10)  PATH '/*/p:code',
            text varchar2(255) PATH '/*/p:text'
     ) xmlt
;

Call Web Service from Oracle

There are a lot of samples how to call web service from Oracle.
but after a small research I make this choice because of speed and simplicity.

So, I create a package and here is a code example how to use it:

select ws_tools.callws(
    --endpoint url
    'http://myhost:9763/services/Dictionary.DictionaryHttpSoap11Endpoint/',
    --soap message payload
    XMLElement(
        "ns1:getEducationTypes",
        XMLAttributes('http://mynamespace/' as "xmlns:ns1"),
        XMLElement("ns1:id",null)
    )
    --optional WSSecurity UsernameToken parameters:
    'theusername',
    'thepassword'
) from dual

The package:

CREATE OR REPLACE PACKAGE WS_TOOLS
    IS
    FUNCTION callWS(p_url IN VARCHAR2,p_method IN XMLTYPE,p_user in VARCHAR2 :=null,p_pass in VARCHAR2 :=null) RETURN XMLTYPE;
END;
/
CREATE OR REPLACE PACKAGE BODY WS_TOOLS
IS


--internal function to post http(s) request
FUNCTION httpPost
(
    p_url     IN VARCHAR2,
    p_data    IN CLOB,
    p_timeout IN BINARY_INTEGER DEFAULT 60
) 
    RETURN CLOB
IS
    --
    v_request  utl_http.req;
    v_response utl_http.resp;
    v_buffer   CLOB;
    v_chunk    VARCHAR2(4000);
    v_length   NUMBER;
    v_index    NUMBER;
BEGIN

    v_index := 1;
    v_length := nvl(length(p_data), 0);

    -- configure HTTP
    utl_http.set_response_error_check(enable => FALSE);
    utl_http.set_detailed_excp_support(enable => FALSE);
    utl_http.set_transfer_timeout(p_timeout);

    -- send request
    v_request := utl_http.begin_request(p_url, 'POST','HTTP/1.0');
    utl_http.set_header(v_request, 'Content-Type', 'text/xml');
    utl_http.set_header(v_request, 'Content-Length', v_length);
    WHILE v_index <= v_length LOOP
        utl_http.write_text(v_request, substr(p_data, v_index, 4000));
        v_index := v_index + 4000;
    END LOOP;

    -- check HTTP status code for error
    IF v_response.status_code <> utl_http.http_ok THEN   
        raise_application_error(-20400,v_response.status_code || ' - ' || v_response.reason_phrase);
    END IF;

    -- get response
    dbms_lob.createtemporary(v_buffer, FALSE);
    v_response := utl_http.get_response(v_request);
    BEGIN
        LOOP
            utl_http.read_text(v_response, v_chunk, 4000);
            dbms_lob.writeappend(v_buffer, length(v_chunk), v_chunk);
        END LOOP;
    EXCEPTION
        WHEN utl_http.end_of_body THEN NULL;
    END;
    utl_http.end_response(v_response);

    RETURN v_buffer;

END;


FUNCTION callWS
(
    p_url IN VARCHAR2,
    p_method IN XMLTYPE,
    p_user in VARCHAR2 :=null,
    p_pass in VARCHAR2 :=null

    --p_timeout IN NUMBER := 60
)
    RETURN XMLTYPE
IS
    -- calls the given SOAP service
    --cn_procedure_name CONSTANT VARCHAR2(30) := 'invoke';
    --
    v_envelope XMLTYPE;
    v_header XMLTYPE;
    v_response CLOB;
    v_fault XMLTYPE;
    v_sqlerrm VARCHAR2(2000);
BEGIN
    if p_user is not null then
        --prepare header
        SELECT
            XMLElement(
                "soap:Header",
                XMLElement(
                    "wsse:Security",
                    XMLAttributes(
                        'http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd' AS "xmlns:wsse"
                    ),
                    XMLElement(
                        "wsse:UsernameToken",
                        XMLAttributes(
                            'UsernameToken-1' AS "wsu:Id",
                            'http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd' AS "xmlns:wsu"
                        ),
                        XMLElement(
                            "wsse:Username",
                            'cfront'
                        ),
                        XMLElement(
                            "wsse:Password",
                            XMLAttributes(
                                'http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText' AS "Type"
                            ),
                            'cfrontpass'
                        )
                    )
                )
            )
        INTO
            v_header
        FROM
            dual;
    end if;


    -- wrap method in SOAP envelope
    SELECT
        XMLElement(
            "soap:Envelope",
            XMLAttributes(
                'http://schemas.xmlsoap.org/soap/envelope/' AS "xmlns:soap"
            ),
            v_header,
            
            XMLElement(
                "soap:Body",
                p_method
            )
        )
    INTO
        v_envelope
    FROM
        dual;

    -- POST request
    v_response := httpPost(
        p_url,
        v_envelope.getClobVal(),
        60 -- 60 seconds
    );
    IF v_response IS NULL THEN
        raise_application_error(-20400,'empty response');
    END IF;

    -- parse response
    BEGIN
        v_envelope := XMLType(v_response);
    EXCEPTION
        WHEN OTHERS THEN
            raise_application_error(-20400,'can''t parse xml: '||SQLERRM);
    END;

    -- check for a fault
    v_fault := v_envelope.extract(  
        '/soap:Envelope/soap:Body/soap:Fault', 
        'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"'
    );
    IF v_fault IS NOT NULL THEN
        v_sqlerrm := v_fault.extract('.//faultstring/text()').getStringVal();
        raise_application_error(-20400,'remote error: '||v_sqlerrm);
    END IF;

    -- the actual response is the child of the "soap:Body" element
    RETURN v_envelope.extract(
        '/soap:Envelope/soap:Body/*[position() = 1]', 
        'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"'
    );
END;

END;