Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table ws_log(id, response) as
select 1, '<?xml version="1.0" encoding="UTF-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <soap:Body>
      <createBillingSubmissionForAFEBSGResponse xmlns="urn:bcpcorp.net/ws/bsgFacets/FacetsBillingService">
         <createBillingSubmissionForAFEBSGResult>
            <status>0</status>
            <outputMessage />
            <statusElement />
            <claimID>18E002021300</claimID>
            <claimStatus>01</claimStatus>
            <claimStatusReason>AGFP</claimStatusReason>
            <totalChargeValue>35.0000</totalChargeValue>
            <totalPayableValue>0.0000</totalPayableValue>
            <paitentPaidvalue>17.5</paitentPaidvalue>
            <totalDebitAmount>0</totalDebitAmount>
            <proceduresList>
               <MedicalProcedureOutput>
                  <ipcdId>011801</ipcdId>
                  <otherdisallowedAmountResponsibility>N</otherdisallowedAmountResponsibility>
               </MedicalProcedureOutput>
            </proceduresList>
         </createBillingSubmissionForAFEBSGResult>
      </createBillingSubmissionForAFEBSGResponse>
   </soap:Body>
</soap:Envelope>' from dual
union all
select 2, '<?xml version="1.0" encoding="UTF-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <soap:Body>
      <createBillingSubmissionForAFEBSGResponse xmlns="urn:bcpcorp.net/ws/bsgFacets/FacetsBillingService">
         <createBillingSubmissionForAFEBSGResult>
            <status>0</status>
            <outputMessage />
            <statusElement />
            <claimID>18E002021300</claimID>
            <claimStatus>01</claimStatus>
            <claimStatusReason>AGFP</claimStatusReason>
            <totalChargeValue>35.0000</totalChargeValue>
            <totalPayableValue>0.0000</totalPayableValue>
            <paitentPaidvalue>17.5</paitentPaidvalue>
            <totalDebitAmount>0</totalDebitAmount>
            <proceduresList>
               <MedicalProcedureOutput>
                  <ipcdId>725013</ipcdId>
          <otherdisallowedAmountResponsibility>N</otherdisallowedAmountResponsibility>
               </MedicalProcedureOutput>
               <MedicalProcedureOutput>
                  <ipcdId>725105</ipcdId>
            <otherdisallowedAmountResponsibility>N</otherdisallowedAmountResponsibility>
               </MedicalProcedureOutput>
            </proceduresList>
         </createBillingSubmissionForAFEBSGResult>
      </createBillingSubmissionForAFEBSGResponse>
   </soap:Body>
</soap:Envelope>' from dual;
2 rows affected

SELECT a.id,
  t1.icpcID FROM
  WS_LOG a,
  xmltable(
  xmlnamespaces(
default 'urn:bcpcorp.net/ws/bsgFacets/FacetsBillingService',
'http://schemas.xmlsoap.org/soap/envelope/' as "soap",
'http://www.w3.org/2001/XMLSchema' as "xsd",
'http://www.w3.org/2001/XMLSchema-instance' as "xsi"
),
'/soap:Envelope/soap:Body/createBillingSubmissionForAFEBSGResponse/createBillingSubmissionForAFEBSGResult/proceduresList/MedicalProcedureOutput' passing xmltype(a.response) columns
icpcID varchar2(50) path 'ipcdId') t1


ID
ICPCID


1
011801

2
725013

2
725105


-- reformatted
SELECT a.id,
  t1.icpcID
FROM WS_LOG a
CROSS JOIN xmltable (
  xmlnamespaces(
    default 'urn:bcpcorp.net/ws/bsgFacets/FacetsBillingService',
      'http://schemas.xmlsoap.org/soap/envelope/' as "soap",
      'http://www.w3.org/2001/XMLSchema' as "xsd",
      'http://www.w3.org/2001/XMLSchema-instance' as "xsi"
  ),
  '/soap:Envelope/soap:Body/createBillingSubmissionForAFEBSGResponse/createBillingSubmissionForAFEBSGResult/proceduresList/MedicalProcedureOutput'
    passing xmltype(a.response)
    columns icpcID varchar2(50) path 'ipcdId'
) t1


ID
ICPCID


1
011801

2
725013

2
725105