4 Replies Latest reply: Jul 16, 2012 8:40 AM by 464787 RSS

    Extracting repeating group from SOAP envelope in PL/SQL

    464787
      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)
          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
            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
              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
                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