Oracle's SOAP Msg with namespace extractValue() Function and XML Namespaces

Question

What am I doing wrong? I am getting an empty response. Please help see the details below

Below I'm creating a table and inserting into it. Then I'm extracting a value of xpath details from the soap message

CREATE TABLE REQUESTS (
    ID NUMBER(10,0) PRIMARY KEY,
    REQUEST XMLTYPE,
    RESPONSE XMLTYPE
);


INSERT INTO REQUESTS (ID, REQUEST, RESPONSE)
VALUES (
    2,  
     '<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:wsa="http://www.w3.org/2005/08/addressing" xmlns:typ="http://xmlns.oracle.com/apps/prc/po/editDocument/purchaseOrderServiceV2/types/">
   <env:Header>
      <wsa:Action>http://xmlns.oracle.com/apps/prc/po/editDocument/purchaseOrderServiceV2//PurchaseOrderService/createPurchaseOrderResponse</wsa:Action>
      <wsa:MessageID>urn:uuid:04b70bff-5a77-4e24-a7a8-e5b217aa4b4a</wsa:MessageID>
   </env:Header>
   <env:Body>
      <ns0:createPurchaseOrderResponse xmlns:ns0="http://xmlns.oracle.com/apps/prc/po/editDocument/purchaseOrderServiceV2/types/">
         <ns1:result xsi:type="ns0:RequestResults" xmlns:ns1="http://xmlns.oracle.com/apps/prc/po/editDocument/purchaseOrderServiceV2/types/" xmlns:ns0="http://xmlns.oracle.com/apps/prc/po/editDocument/purchaseOrderServiceV2/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
            <ns0:POHeaderId>300000000694092</ns0:POHeaderId>
            <ns0:OrderNumber>27</ns0:OrderNumber>
            <ns0:SoldToLegalEntityId>300000000639014</ns0:SoldToLegalEntityId>
            <ns0:ChangeOrderNumber xsi:nil="true"/>
            <ns0:RequestStatus>SUCCESS</ns0:RequestStatus>
         </ns1:result>
      </ns0:createPurchaseOrderResponse>
   </env:Body>
</env:Envelope>', null);




   SELECT
    req.id,
    extractValue(
        req.request, 
    '/env:Envelope/env:Body/ns0:createPurchaseOrderResponse/ns1:result/ns0:OrderNumber',
         'xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns0="http://xmlns.oracle.com/apps/prc/po/editDocument/purchaseOrderServiceV2/types/" xmlns:ns1="http://xmlns.oracle.com/apps/prc/po/editDocument/purchaseOrderServiceV2/types/"  xmlns:ns0="http://xmlns.oracle.com/apps/prc/po/editDocument/purchaseOrderServiceV2/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" ') Title
FROM 
    requests req
WHERE
    id = 2

Answer 1

Look at the last argument to extractValue(), which specifies namespace prefix declarations:

'xmlns:env="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:ns0="http://xmlns.oracle.com/apps/prc/po/editDocument/purchaseOrderServiceV2/types/"
xmlns:ns1="http://xmlns.oracle.com/apps/prc/po/editDocument/purchaseOrderServiceV2/types/" 
xmlns:ns0="http://xmlns.oracle.com/apps/prc/po/editDocument/purchaseOrderServiceV2/"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" '

(line breaks added for legibility).

The namespace prefix ns0 is declared twice, with two different namespace URI values... one of which is shared with ns1! This follows the usage in the XML (which is understandable but not necessary), and makes it hard (or impossible?) to be sure which definition of ns0 is actually in effect when the XPath expression is evaluated.

To fix it, remove the first ns0 declaration, since the ".../types" namespace is already covered by ns1. Then change your XPath expression to

'/env:Envelope/env:Body/ns1:createPurchaseOrderResponse/ns1:result/ns0:OrderNumber'

If you know how namespace declarations work, you can follow the XML and see why this will fix the problem. If not, it's worth learning, if you work with XML vocabularies, like SOAP, that use namespaces. Try one of these:

• http://www.xml.com/pub/a/1999/01/namespaces.html
• http://books.xmlschemata.org/relaxng/relax-CHP-11-SECT-1.html
• http://www.xmlmaster.org/en/article/d01/c10/

Do you have control over the XML and how it's serialized? There's nothing technically wrong with it, but the choice of namespace prefix declarations seems almost designed to cause human error. If you have any control over it, you should pick mnemonic prefixes and use them consistently, instead of changing them as the current XML does.