SQL & PL/SQL

My requirement is to extract the soap envelope from a clob. I need to extract all the text that is between '<s:Envelope' and 's:Envelope>'. That means I need to get rid of the first 5 lines and the last line from the l_response. Can anyone please help me with the logic?

DECLARE

   l_request             VARCHAR2 (4000);

   l_http_req            UTL_HTTP.req;

   l_http_resp           UTL_HTTP.resp;

   v_buffer              VARCHAR2 (32767);

   n_next_start_record   NUMBER (20) := 1;

   l_response            CLOB;

BEGIN

   -- Call webservices. Works fine

   l_request :=

         '--uuid:e4c19840-745d-45b2-90ca-12d71be4cfd9+id=2'

      || CHR (13)

      || CHR (10)

      || 'Content-ID: <http://tempuri.org/0>'

      || CHR (13)

      || CHR (10)

      || 'Content-Transfer-Encoding: 8bit'

      || CHR (13)

      || CHR (10)

      || 'Content-Type: application/xop+xml;charset=utf-8;type="application/soap+xml"'

      || CHR (13)

      || CHR (10)

      || CHR (13)

      || CHR (10)

      || '<s:Envelope xmlns:s="http://www.w3.org/2003/05/soap-envelope" xmlns:a="http://www.w3.org/2005/08/addressing"><s:Header><a:Action s:mustUnderstand="1">http://tempuri.org/IUpdateService/QueryUpdateLogRecords</a:Action><a:MessageID>urn:uuid:413f419c-f489-44ea-bd12-dff6f24a4d71</a:MessageID><a:ReplyTo><a:Address>http://www.w3.org/2005/08/addressing/anonymous</a:Address></a:ReplyTo><a:To s:mustUnderstand="1">http://dexdb5/DexNETWebServices_4_0_0_4/UpdateService.svc</a:To></s:Header><s:Body><QueryUpdateLogRecords xmlns="http://tempuri.org/"><context xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns:x="http://www.w3.org/2001/XMLSchema"><XObject.m_element i:type="x:string" xmlns="">&lt;OnlineContext SystemId="'

      || g_system_id

      || '" SessionId="'

      || g_session_id

      || '" UserId="'

      || g_user_id

      || '" /&gt;</XObject.m_element></context><xQueryRequest xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns:x="http://www.w3.org/2001/XMLSchema"><XObject.m_element i:type="x:string" xmlns="">&lt;QueryRequest Start="'

      || p_next_start_record

      || '" Count="'

      || g_records_count

      || '" Distinct="0" OrderBy="" Condition="(oUpdateLog.DateCreated &amp;gt;= '''

      || p_last_load_time

      || ''')" ColumnInfo="1" /&gt;</XObject.m_element></xQueryRequest></QueryUpdateLogRecords></s:Body></s:Envelope>'

      || CHR (13)

      || CHR (10)

      || '--uuid:e4c19840-745d-45b2-90ca-12d71be4cfd9+id=2--';

   l_http_req :=

      UTL_HTTP.begin_request (g_query_updatelog_records_url, 'POST', 'HTTP/1.1');

   UTL_HTTP.set_header (l_http_req, 'MIME-Version', '1.0');

   UTL_HTTP.set_header (

      l_http_req,

      'Content-Type',

      'multipart/related; type="application/xop+xml";start="<http://tempuri.org/0>";boundary="uuid:e4c19840-745d-45b2-90ca-12d71be4cfd9+id=2";start-info="application/soap+xml"');

   UTL_HTTP.set_header (

      l_http_req,

      'VsDebuggerCausalityData',

      'uIDPo5F/qXRc4YJImqB6Ard30cQAAAAAAjIXinpIVUulXLJOsSG7yyv7Lf2yHgpHlIxvc6oeqaAACQAA');

   UTL_HTTP.set_header (l_http_req, 'Content-Length', LENGTH (l_request));

   UTL_HTTP.write_text (l_http_req, l_request);

   DBMS_LOB.createtemporary (l_response, FALSE);

   l_http_resp := UTL_HTTP.get_response (l_http_req);

   BEGIN

      LOOP

         UTL_HTTP.read_text (l_http_resp, v_buffer, 32767);

         DBMS_OUTPUT.put_line (v_buffer);

         DBMS_LOB.writeappend (l_response, LENGTH (v_buffer), v_buffer);

      END LOOP;

   EXCEPTION

      WHEN UTL_HTTP.end_of_body

      THEN

         NULL;

   END;

   UTL_HTTP.end_response (l_http_resp);

   l_response := DBMS_XMLGEN.CONVERT (xmldata => l_response, flag => 1);

   -- Extract the soap envelope from clob. Issue because of the 32767 characters limitation

   SELECT    DBMS_LOB.SUBSTR (                  -- Problem here

                l_response,

                  INSTR (l_response, 's:Envelope>', -1)

                - INSTR (l_response, '<s:Envelope'),

                INSTR (l_response, '<s:Envelope'))

          || 's:Envelope>'

     INTO l_response

     FROM DUAL;

      -- Parse the xml. Works fine once the above issue is fixed

      SELECT xt.nextstart

        INTO n_next_start_record

        FROM XMLTABLE (

                xmlnamespaces ('http://www.w3.org/2003/05/soap-envelope' AS "s",

                               'http://tempuri.org/' AS "data"),

                's:Envelope/s:Body/data:QueryUpdateLogRecordsResponse/data:QueryUpdateLogRecordsResult/XObject.m_element/QueryResult'

                PASSING xmltype (l_response)

                COLUMNS nextstart NUMBER (20) PATH '@NextStart') xt;

   DBMS_OUTPUT.put_line ('NextStart ' || n_next_start_record);

END;

/

In the above code l_response returned from the http request is a clob that looks like:

--uuid:18cb22a2-11cc-43f4-bfea-c213da179d30+id=157

Content-ID: <http://tempuri.org/0>

Content-Transfer-Encoding: 8bit

Content-Type: application/xop+xml;charset=utf-8;type="application/soap+xml"

<s:Envelope xmlns:s="http://www.w3.org/2003/05/soap-envelope" xmlns:a="http://www.w3.org/2005/08/addressing"><s:Header><a:Action s:mustUnderstand="1">http://tempuri.org/IUpdateService/QueryUpdateLogRecordsResponse</a:Action><a:RelatesTo>urn:uuid:413f419c-f489-44ea-bd12-dff6f24a4d71</a:RelatesTo></s:Header><s:Body><QueryUpdateLogRecordsResponse xmlns="http://tempuri.org/"><QueryUpdateLogRecordsResult xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns:x="http://www.w3.org/2001/XMLSchema"><XObject.m_element i:type="x:string" xmlns="">&lt;QueryResult Count="2" NextStart="0" PreviousStart="0" Id="{AD62FD77-AFBE-4362-BBEF-695DA5D92640}"&gt;&lt;Columns Count="33"&gt;&lt;Column AttributeName="Id" 

… 5 pages later…

DateModified="2014-07-06 07:34:41.9129549-07:00" /&gt;&lt;/Records&gt;&lt;/QueryResult&gt;</XObject.m_element></QueryUpdateLogRecordsResult></QueryUpdateLogRecordsResponse></s:Body></s:Envelope>

--uuid:18cb22a2-11cc-43f4-bfea-c213da179d30+id=157--