Unable to get the value from SOAP Webservice Response using XMLQuery

I'm using Oracle's XMLQuery database function to read a SOAP Webservice response, however the response element "result" includes namespace clause (xmlns="http://xmlns.oracle.com/apps/hcm/processFlows/core/flowActionsService/types/") next to the element name, so the XMLQuery fails to read the element's value.

This is the Webservice response body I received, and I'm trying to read the "result"

<env:Body>
        <ns0:getFlowTaskInstanceStatusResponse xmlns:ns0="http://xmlns.oracle.com/apps/hcm/processFlows/core/flowActionsService/types/">
        <result xmlns="http://xmlns.oracle.com/apps/hcm/processFlows/core/flowActionsService/types/">COMPLETED</result>
        </ns0:getFlowTaskInstanceStatusResponse>
</env:Body>

.
The following Select statement returns NULL


SELECT xmlcast(XMLQuery('//result' PASSING l_xmldata RETURNING CONTENT) as varchar2(900))
  into l_extract
  from dual;

The XMLQuery function will return "COMPLETED" in case I remove the namespace text from the XML response !!! However this is not a practical workaround.

BR Hany

Solution

You have two options (I added an "env" namespace to make the xml valid):

include the namespaces in the query:

WITH test AS
  (SELECT xmltype(' 
<env:Body xmlns:env="myenv">        
<ns0:getFlowTaskInstanceStatusResponse xmlns:ns0="http://xmlns.oracle.com/apps/hcm/processFlows/core/flowActionsService/types/">        
<result xmlns="http://xmlns.oracle.com/apps/hcm/processFlows/core/flowActionsService/types/">COMPLETED</result>        
</ns0:getFlowTaskInstanceStatusResponse>
</env:Body>') AS data
  FROM dual
  )
SELECT CAST( extractValue(test.data, '//ns0:result','xmlns:env="myenv" xmlns:ns0="http://xmlns.oracle.com/apps/hcm/processFlows/core/flowActionsService/types/') AS VARCHAR2(900) )
FROM test

ignore the namespaces

WITH test AS
  (SELECT xmltype(' 
<env:Body xmlns:env="myenv">        
<ns0:getFlowTaskInstanceStatusResponse xmlns:ns0="http://xmlns.oracle.com/apps/hcm/processFlows/core/flowActionsService/types/">        
<result xmlns="http://xmlns.oracle.com/apps/hcm/processFlows/core/flowActionsService/types/">COMPLETED</result>        
</ns0:getFlowTaskInstanceStatusResponse>
</env:Body>') AS data
  FROM dual
  )
SELECT CAST( extractValue(test.data, '//*[local-name() = "result"]') AS VARCHAR2(900) )
FROM test