This discussion is archived
4 Replies Latest reply: Jul 16, 2012 6:40 AM by 464787 RSS

Extracting repeating group from SOAP envelope in PL/SQL

464787 Newbie
Currently Being Moderated
Hi all.

I have the following SOAP envelope in an XMLTYPE variable in PL/SQL.

<?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" xmlns:ns0="http://elmarwsoicdvmws/types/">
<env:Body>
<ns0:getDVMValuesResponseElement>
<ns0:result>
<ns0:dvmValues>10</ns0:dvmValues>
<ns0:dvmValues>11</ns0:dvmValues>
<ns0:dvmValues>12</ns0:dvmValues>
<ns0:errorText>Ok</ns0:errorText>
<ns0:errorCode>0</ns0:errorCode>
</ns0:result>
</ns0:getDVMValuesResponseElement>
</env:Body>
</env:Envelope>'

I can extract the errorCode and ErrorText variables without too much effort (though I'm sure there are easier ways), but now I have to extract the repeating group of dvmValues into an array type variable.

Any ideas/code snippets would be greatly appreciated, as I've been reading tons of docs and half the time they are too complex or too simplistic (given my ns0: variables), and it seems a lot of the functions I've been using have been depricated.

Kind regards

Elmar
  • 1. Re: Extracting repeating group from SOAP envelope in PL/SQL
    Jason_(A_Non) Expert
    Currently Being Moderated
    Depends upon your version and size of the response as to what may be best (you knew there was no one simple answer ;-) )

    If your version supports XMLTable and the response is not large
    {message:id=10195752}

    If no XMLTable support
    {thread:id=2390962}

    If 11g, you could drop the response into a temporary table with a SECUREFILE BINARY XML column and use XMLTable (or XQuery) to parse it
    {thread:id=2387358}

    That should be enough examples to get you started. If you get hung up, post what you have, including version, and then we offer more assistance.
  • 2. Re: Extracting repeating group from SOAP envelope in PL/SQL
    464787 Newbie
    Currently Being Moderated
    Hi A_Non.

    Here is my version information:

    Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
    PL/SQL Release 10.2.0.5.0 - Production
    CORE     10.2.0.5.0     Production
    TNS for Linux: Version 10.2.0.5.0 - Production
    NLSRTL Version 10.2.0.5.0 - Production

    Currently my function looks as follows. I've so far extracted the ErrorCode and ErrorText values out of the XMLTYPE response. I'll include the function that does the actual extraction here as well just so you can see my current setup. I'm still working through your suggestions (thanks btw).
    FUNCTION GET_DVM_VALUES(DVM_TYPE IN VARCHAR2, ADAPTER IN VARCHAR2) 
      RETURN VARCHAR2 AS
    
    /******************************************************************************
       NAME:       GET_DVM_VALUES
       PURPOSE:    Returns list of all values of a specified DVM list for a 
                   specified adapter
       REVISIONS:
       Ver        Date        Author              Description
       ---------  ----------  ---------------    ------------------------------------
       1.0        2012/07/13  Elmar Matthee      1. Created this function.
    
    ******************************************************************************/
    -- Http Req Variables
          http_req       UTL_HTTP.req;
          http_resp      UTL_HTTP.resp;
          request_env    VARCHAR2 (32767);
          response_env   VARCHAR2 (32767);
          resp           XMLTYPE;
    -- Error Vars
          e_code         NUMBER;
          e_errm         VARCHAR2 (64);
    -- Return variable
    rep VARCHAR2(300);
    wsEndpoint WS_ENDPOINTS%ROWTYPE;
    obj_status VARCHAR2(100);
    
    -- WS Variables
       wsUrl VARCHAR2(150) := 'http://v240-02.sun.ac.za:7784/OICDVMWS/OICDVMWSSoapHttpPort';
    
    
    BEGIN
      wsEndpoint := GET_WS_ENDPOINT('GET_DVM_VALUES');
      wsUrl := wsEndpoint.WS_ENDPOINT;
      rep := '';
      
    /* --- This is the packet sent to the WS
    */ 
       request_env :=
       '<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" xmlns:ns0="http://elmarwsoicdvmws/types/">
          <env:Body>
            <ns0:getDVMValuesElement>
             <ns0:dvmType>' || DVM_TYPE  || '</ns0:dvmType>
             <ns0:adapter>' || ADAPTER || '</ns0:adapter>
            </ns0:getDVMValuesElement>
          </env:Body>
        </env:Envelope>';
      http_req :=
             UTL_HTTP.begin_request
                             (wsUrl,
                              'POST',
                              UTL_HTTP.http_version_1_1
                             );
          UTL_HTTP.set_header (http_req, 'Content-Type',
                               'text/xml; charset=iso-8859-1');
          UTL_HTTP.set_header (http_req, 'Content-Length', LENGTH (request_env));
          UTL_HTTP.set_header (http_req,
                               'SOAPAction',
                               'http://elmarwsoicdvmws//getDVMValues'
                              );
          UTL_HTTP.write_text (http_req, request_env);
          http_resp := UTL_HTTP.get_response (http_req); 
          
          IF TRIM (http_resp.status_code) = '200'
            THEN
             UTL_HTTP.read_text (http_resp, response_env);
             response_env :=
                    DBMS_XMLGEN.CONVERT (response_env, DBMS_XMLGEN.entity_decode);
             -- Extract the ErrorCode and ErrorText values
             resp := XMLTYPE.createxml (response_env);
             obj_status := extract_xml_element_value (resp, 'errorCode');
             rep := obj_status || ' ' || extract_xml_element_value (resp, 'errorText');
          ELSE
            rep := http_resp.status_code || ' ' || http_resp.reason_phrase;
          END IF;
          
          UTL_HTTP.end_response (http_resp);
       
       
       RETURN rep;
       EXCEPTION
          WHEN OTHERS
          THEN
            e_code := SQLCODE;
            e_errm := SUBSTR (SQLERRM, 1,64);
            rep := e_code || ' ' || SUBSTR (SQLERRM, 1, 250);
            --rep.ERRORCODE := TO_NUMBER(e_code);
            --rep.ERRORTEXT := SUBSTR (SQLERRM, 1, 250);
            RETURN rep;
            
    
    END GET_DVM_VALUES;
    
       FUNCTION extract_xml_element_value (
          xml_fragment   IN   XMLTYPE,
          element_name   IN   VARCHAR2
       )
          RETURN VARCHAR2
       AS
          doc          DBMS_XMLDOM.domdocument;
          ndoc         DBMS_XMLDOM.domnode;
          docelem      DBMS_XMLDOM.domelement;
          node         DBMS_XMLDOM.domnode;
          childnode    DBMS_XMLDOM.domnode;
          nodelist     DBMS_XMLDOM.domnodelist;
          var          NUMBER;
          respstring   VARCHAR2 (32767);
       BEGIN
          -- Create DOMDocument handle
          doc := DBMS_XMLDOM.newdomdocument (xml_fragment);
          ndoc := DBMS_XMLDOM.makenode (doc);
          docelem := DBMS_XMLDOM.getdocumentelement (doc);
          -- Access element
          nodelist := DBMS_XMLDOM.getelementsbytagname (docelem, element_name);
          node := DBMS_XMLDOM.item (nodelist, 0);
          childnode := DBMS_XMLDOM.getfirstchild (node);
          respstring := DBMS_XMLDOM.getnodevalue (childnode);
          DBMS_XMLDOM.freedocument (doc);
          --
          RETURN respstring;
       END extract_xml_element_value;
    
    SELECT XXWS_CLIENTS.GET_DVM_VALUES('TITLE_CODE','NATX') FROM DUAL;
    
    0 Ok
    Any further comments/suggestions would be most welcome.

    Kind regards

    Elmar

    Edited by: Elmar Matthee on Jul 16, 2012 11:28 AM
  • 3. Re: Extracting repeating group from SOAP envelope in PL/SQL
    odie_63 Guru
    Currently Being Moderated
    Hi Elmar,

    Using a separate function to parse individual nodes is not necessary.
    You can extract everything with a single query, thus eliminating the need to access the same document multiple times.

    For example, once you have the content in the resp xmltype variable, you can do :
    SQL> create type dvmValues_t as table of number;
      2  /
     
    Type created
     
    SQL> set serveroutput on
    SQL> 
    SQL> DECLARE
      2  
      3    resp         xmltype;
      4    v_errorText  varchar2(2000);
      5    v_errorCode  number;
      6    v_dvmValues  dvmValues_t;
      7  
      8  BEGIN
      9  
     10    resp := xmltype('<?xml version = "1.0" encoding = "UTF-8"?>
     11  <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" xmlns:ns0="http://elmarwsoicdvmws/types/">
     12  <env:Body>
     13  <ns0:getDVMValuesResponseElement>
     14  <ns0:result>
     15  <ns0:dvmValues>10</ns0:dvmValues>
     16  <ns0:dvmValues>11</ns0:dvmValues>
     17  <ns0:dvmValues>12</ns0:dvmValues>
     18  <ns0:errorText>Ok</ns0:errorText>
     19  <ns0:errorCode>0</ns0:errorCode>
     20  </ns0:result>
     21  </ns0:getDVMValuesResponseElement>
     22  </env:Body>
     23  </env:Envelope>');
     24  
     25    select x.errorText
     26         , x.errorCode
     27         , cast(
     28             multiset(
     29               select dvmValue
     30               from xmltable('*' passing x.dvmValues
     31                                 columns dvmValue number path '.')
     32             )
     33             as dvmValues_t
     34           )
     35    into v_errorText
     36       , v_errorCode
     37       , v_dvmValues
     38    from xmltable(
     39           xmlnamespaces(
     40             'http://schemas.xmlsoap.org/soap/envelope/' as "env"
     41           , default 'http://elmarwsoicdvmws/types/'
     42           )
     43         , '/env:Envelope/env:Body/getDVMValuesResponseElement/result'
     44           passing resp
     45           columns
     46             errorText  varchar2(2000)  path 'errorText'
     47           , errorCode  number          path 'errorCode'
     48           , dvmValues  xmltype         path 'dvmValues'
     49         ) x
     50    ;
     51  
     52    dbms_output.put_line('Error Text = ' || v_errorText);
     53    dbms_output.put_line('Error Code = ' || v_errorCode);
     54  
     55    for i in 1..v_dvmValues.count loop
     56      dbms_output.put_line('Value(' || to_char(i) || ') = ' || v_dvmValues(i));
     57    end loop;
     58  
     59  END;
     60  /
     
    Error Text = Ok
    Error Code = 0
    Value(1) = 10
    Value(2) = 11
    Value(3) = 12
     
    PL/SQL procedure successfully completed
     
  • 4. Re: Extracting repeating group from SOAP envelope in PL/SQL
    464787 Newbie
    Currently Being Moderated
    Hi odie_63

    Thanks for this. Works like a charm. I'll work through the code so that I can understand what you did where and why, but thanks a million.

    Regards.

    Elmar

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points