Accessing values in XML from a variable in Oracle SQL

I have a XML data as below in a variable p_val:

<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/"
    xmlns:wsa="http://www.w3.org/2005/08/addressing">
    <env:Header>
        <wsa:MessageID>urn:bda29066-5961-11ec-87ec-0242c5d8b376</wsa:MessageID>
        <wsa:ReplyTo>
            <wsa:Address>http://www.w3.org/2005/08/addressing/anonymous</wsa:Address>
            <wsa:ReferenceParameters>
                <instra:tracking.ecid xmlns:instra="http://xmlns.oracle.com/sca/tracking/1.0">9b657011-42e4-4cf4-a78e-537551de4cc0-0057ca4a</instra:tracking.ecid>
                <instra:tracking.FlowEventId xmlns:instra="http://xmlns.oracle.com/sca/tracking/1.0">27818206</instra:tracking.FlowEventId>
                <instra:tracking.FlowId xmlns:instra="http://xmlns.oracle.com/sca/tracking/1.0">169007626</instra:tracking.FlowId>
                <instra:tracking.CorrelationFlowId xmlns:instra="http://xmlns.oracle.com/sca/tracking/1.0">0000NqYCMWFDGfdLxeG7yW1X_Nsm0003Yk</instra:tracking.CorrelationFlowId>
                <instra:tracking.quiescing.SCAEntityId xmlns:instra="http://xmlns.oracle.com/sca/tracking/1.0">1830036</instra:tracking.quiescing.SCAEntityId>
            </wsa:ReferenceParameters>
        </wsa:ReplyTo>
        <wsa:FaultTo>
            <wsa:Address>http://www.w3.org/2005/08/addressing/anonymous</wsa:Address>
        </wsa:FaultTo>
    </env:Header>
    <env:Body>
        <LaunchSpreadSheetWorkFlowResponse xmlns="http://xmlns.oracle.com/bpmn/bpmnCloudProcess/ISV_ROYALTIES/ISVSpreadSheetMainProcess">
            <instanceNumber xmlns:def="http://www.w3.org/2001/XMLSchema" xsi:type="def:long"
                xmlns=""
                xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">79630772</instanceNumber>
        </LaunchSpreadSheetWorkFlowResponse>
    </env:Body>
</env:Envelope>

I want to access the value of the tag: instanceNumber which is 79630772.

If it is a direct payload instead of a variable I tried accessing it using:

select *  
FROM XMLTABLE('/Envelope/Body/LaunchSpreadSheetWorkFlowResponse'  
         PASSING   
            xmltype('
                <env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/"
    xmlns:wsa="http://www.w3.org/2005/08/addressing">
    <env:Header>
        <wsa:MessageID>urn:bda29066-5961-11ec-87ec-0242c5d8b376</wsa:MessageID>
        <wsa:ReplyTo>
            <wsa:Address>http://www.w3.org/2005/08/addressing/anonymous</wsa:Address>
            <wsa:ReferenceParameters>
                <instra:tracking.ecid xmlns:instra="http://xmlns.oracle.com/sca/tracking/1.0">9b657011-42e4-4cf4-a78e-537551de4cc0-0057ca4a</instra:tracking.ecid>
                <instra:tracking.FlowEventId xmlns:instra="http://xmlns.oracle.com/sca/tracking/1.0">27818206</instra:tracking.FlowEventId>
                <instra:tracking.FlowId xmlns:instra="http://xmlns.oracle.com/sca/tracking/1.0">169007626</instra:tracking.FlowId>
                <instra:tracking.CorrelationFlowId xmlns:instra="http://xmlns.oracle.com/sca/tracking/1.0">0000NqYCMWFDGfdLxeG7yW1X_Nsm0003Yk</instra:tracking.CorrelationFlowId>
                <instra:tracking.quiescing.SCAEntityId xmlns:instra="http://xmlns.oracle.com/sca/tracking/1.0">1830036</instra:tracking.quiescing.SCAEntityId>
            </wsa:ReferenceParameters>
        </wsa:ReplyTo>
        <wsa:FaultTo>
            <wsa:Address>http://www.w3.org/2005/08/addressing/anonymous</wsa:Address>
        </wsa:FaultTo>
    </env:Header>
    <env:Body>
        <LaunchSpreadSheetWorkFlowResponse xmlns="http://xmlns.oracle.com/bpmn/bpmnCloudProcess/ISV_ROYALTIES/ISVSpreadSheetMainProcess">
            <instanceNumber xmlns:def="http://www.w3.org/2001/XMLSchema" xsi:type="def:long"
                xmlns=""
                xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">79630772</instanceNumber>
                <name>XYZ</name>
        </LaunchSpreadSheetWorkFlowResponse>
    </env:Body>
</env:Envelope>
            ')
         COLUMNS  
            temp2 varchar2(20)     PATH './instanceNumber'
     ) xmlt  
;

The above query returned an empty value.

How should I access the value from a variable?

Solution

You need to specify and supply the namespaces:

select *  
FROM XMLTABLE(
         XMLNAMESPACES(
            default 'http://xmlns.oracle.com/bpmn/bpmnCloudProcess/ISV_ROYALTIES/ISVSpreadSheetMainProcess',
            'http://schemas.xmlsoap.org/soap/envelope/' AS "env"
         ),
         '/env:Envelope/env:Body/LaunchSpreadSheetWorkFlowResponse'  
         PASSING   
         ...
         COLUMNS  
            temp2 varchar2(20)     PATH '*:instanceNumber'
     ) xmlt  
;

The Envelope and Body come under the SOAP env namespace. In the XML document the LaunchSpreadSheetWorkFlowResponse node doesn't have a namespace prefix but defines a default namespace, so that default needs to be supplied too. Then it gets slightly awkward for instanceNumber as that node redefines (or clears) the default, and defines additional namespaces, but doesn't use them - which puts that node in limbo a bit. I've taken the easy route and wildcarded that with the *: prefix.

The less lazy way, I think, is to treat the LaunchSpreadSheetWorkFlowResponse as a separate XML fragment with a seconf XMLTable call:

select xmlt2.instanceNumber
FROM XMLTABLE(
         XMLNAMESPACES(
            default 'http://xmlns.oracle.com/bpmn/bpmnCloudProcess/ISV_ROYALTIES/ISVSpreadSheetMainProcess',
            'http://schemas.xmlsoap.org/soap/envelope/' AS "env"
         ),
         '/env:Envelope/env:Body/LaunchSpreadSheetWorkFlowResponse'  
         PASSING   
         ...
         COLUMNS  
            LaunchSpreadSheetWorkFlowResponse xmlType     PATH '.'
     ) xmlt1
CROSS JOIN XMLTABLE(
         '.'  
         PASSING   
            xmlt1.LaunchSpreadSheetWorkFlowResponse
         COLUMNS  
            instanceNumber varchar2(20)     PATH 'instanceNumber'
     ) xmlt2
;

I'm not sure it's worth it here, and I'd be tempted to stick to the wildcard version.

db<>fiddle showing both.