Extracting values from XML column in Oracle

I have some data in an Oracle table which is stored in an XML-format string (column response in table WS_LOG).

I would like to extract data from each different node below <MedicalProcedureOutput>, but I’m having some difficulties in getting to each of the nodes. Can you spot what I’m doing wrong?

Here’s what I’m trying (here trying to retrieve the value for the icpcID tag):

 SELECT a.id,
  t1.icpcID FROM
  GH.WS_LOG a, 
  xmltable(
  xmlnamespaces(
'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

And here’s some example data

<?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>

I would expect to be getting the value ‘011801’. Please note that multiple <MedicalProcedureOutput> nodes may occur, and they would be organized as follows:

               <MedicalProcedureOutput>
                  <ipcdId>725013</ipcdId>     
          <otherdisallowedAmountResponsibility>N</otherdisallowedAmountResponsibility>
               </MedicalProcedureOutput>

               <MedicalProcedureOutput>
                  <ipcdId>725105</ipcdId>   
            <otherdisallowedAmountResponsibility>N</otherdisallowedAmountResponsibility>
               </MedicalProcedureOutput>

Answer

You need to declare a default namespace for everything from within the body, since createBillingSubmissionForAFEBSGResponse has its own xmlns unnamed (therefore default) declaration which applies from that node onwards; so:

SELECT a.id,
  t1.icpcID FROM
  GH.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

or with multiple nodes as you showed later int he question this will return:

        ID ICPCID
---------- --------------------------------------------------
         2 725013
         2 725105

db<>fiddle