Test whether value exists (node exists?) in xmltype variable (not in table) in PL/SQL

Question

I have a resulting variable from a SOAP call in PL/SQL, and the out type variable I get back is xmltype (didn't write the package or procedure, don't get to change it). The variable type I get back is xmltype, and I want to check whether there was a result or not. I'm having trouble understanding the docs around XML in PL/SQL.

The account number will always exist, what I'm checking for is whether a location was returned or not. Or more than one being returned (which would be awful, but I should check for it).

Result looks like

<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:wsa="http://www.w3.org/2005/08/addressing">
   <env:Header>
      <wsa:Action>http://xmlns.oracle.com/apps/cdm/foundation/parties/customerAccountService/applicationModule//CustomerAccountService/findCustomerAccountResponse</wsa:Action>
      <wsa:MessageID>urn:uuid:7dd64ecf-676f-4667-a761-dc7603e99929</wsa:MessageID>
   </env:Header>
   <env:Body>
      <ns0:findCustomerAccountResponse xmlns:ns0="http://xmlns.oracle.com/apps/cdm/foundation/parties/customerAccountService/applicationModule/types/">
         <ns0:result xsi:type="ns2:CustomerAccountResult" xmlns:ns2="http://xmlns.oracle.com/apps/cdm/foundation/parties/customerAccountService/" xmlns:ns1="http://xmlns.oracle.com/adf/svc/types/" xmlns:ns4="http://xmlns.oracle.com/apps/cdm/foundation/parties/partyService/" xmlns:tns="http://xmlns.oracle.com/adf/svc/errors/" xmlns:ns9="http://xmlns.oracle.com/apps/cdm/foundation/parties/flex/custAccount/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
            <ns1:Message>
               <tns:code>25089</tns:code>
               <tns:message>JBO-25089: Too many matching records found. Specify additional criteria to limit the number of records.</tns:message>
               <tns:severity>SEVERITY_WARNING</tns:severity>
            </ns1:Message>
            <ns2:Value>
               <ns2:AccountNumber>XXXXX</ns2:AccountNumber>
               <ns2:CustomerAccountSite xmlns:ns7="http://xmlns.oracle.com/apps/cdm/foundation/parties/flex/custAccountSite/"/>
               <ns2:CustomerAccountSite xmlns:ns7="http://xmlns.oracle.com/apps/cdm/foundation/parties/flex/custAccountSite/">
                  <ns2:CustomerAccountSiteUse xmlns:ns6="http://xmlns.oracle.com/apps/cdm/foundation/parties/flex/custAccountSiteUse/">
                     <ns2:Location>XXXXXXXX</ns2:Location>
                  </ns2:CustomerAccountSiteUse>
               </ns2:CustomerAccountSite>
               <ns2:CustomerAccountSite xmlns:ns7="http://xmlns.oracle.com/apps/cdm/foundation/parties/flex/custAccountSite/"/>
               <ns2:CustomerAccountSite xmlns:ns7="http://xmlns.oracle.com/apps/cdm/foundation/parties/flex/custAccountSite/"/>
            </ns2:Value>
         </ns0:result>
      </ns0:findCustomerAccountResponse>
   </env:Body>
</env:Envelope>

I think something like

IF DBMS_LOB.INSTR(xml_response.getClobVal(), 'Location>XXXXXX<') > 0 THEN
     val_returned := TRUE;
END IF;

would work, but I think it's probably a bad way of doing it? I've been trying to read the docs, but they all reference doing selects from a table and the like, and I don't have a table. Can I use xml expressions on these things without referencing a table? Do I have to select from dual or something?

Answer 1

Yes, XPath in Oracle is not pretty, especially if namespaces are involved. I'm not good at it, but maybe this gets you started in the right direction:

DECLARE
  accountnumber VARCHAR2(100);
  location      VARCHAR2(100);
  x XMLType := XMLType('
<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:wsa="http://www.w3.org/2005/08/addressing">
   <env:Header>
      <wsa:Action>http://xmlns.oracle.com/apps/cdm/foundation/parties/customerAccountService/applicationModule//CustomerAccountService/findCustomerAccountResponse</wsa:Action>
      <wsa:MessageID>urn:uuid:7dd64ecf-676f-4667-a761-dc7603e99929</wsa:MessageID>
   </env:Header>
   <env:Body>
      <ns0:findCustomerAccountResponse xmlns:ns0="http://xmlns.oracle.com/apps/cdm/foundation/parties/customerAccountService/applicationModule/types/">
         <ns0:result xsi:type="ns2:CustomerAccountResult" xmlns:ns2="http://xmlns.oracle.com/apps/cdm/foundation/parties/customerAccountService/" xmlns:ns1="http://xmlns.oracle.com/adf/svc/types/" xmlns:ns4="http://xmlns.oracle.com/apps/cdm/foundation/parties/partyService/" xmlns:tns="http://xmlns.oracle.com/adf/svc/errors/" xmlns:ns9="http://xmlns.oracle.com/apps/cdm/foundation/parties/flex/custAccount/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
            <ns1:Message>
               <tns:code>25089</tns:code>
               <tns:message>JBO-25089: Too many matching records found. Specify additional criteria to limit the number of records.</tns:message>
               <tns:severity>SEVERITY_WARNING</tns:severity>
            </ns1:Message>
            <ns2:Value>
               <ns2:AccountNumber>XXXXX</ns2:AccountNumber>
               <ns2:CustomerAccountSite xmlns:ns7="http://xmlns.oracle.com/apps/cdm/foundation/parties/flex/custAccountSite/"/>
               <ns2:CustomerAccountSite xmlns:ns7="http://xmlns.oracle.com/apps/cdm/foundation/parties/flex/custAccountSite/">
                  <ns2:CustomerAccountSiteUse xmlns:ns6="http://xmlns.oracle.com/apps/cdm/foundation/parties/flex/custAccountSiteUse/">
                     <ns2:Location>XXXXXXXX</ns2:Location>
                     <ns2:Location>YYYYYYYY</ns2:Location>
                  </ns2:CustomerAccountSiteUse>
               </ns2:CustomerAccountSite>
               <ns2:CustomerAccountSite xmlns:ns7="http://xmlns.oracle.com/apps/cdm/foundation/parties/flex/custAccountSite/"/>
               <ns2:CustomerAccountSite xmlns:ns7="http://xmlns.oracle.com/apps/cdm/foundation/parties/flex/custAccountSite/"/>
            </ns2:Value>
         </ns0:result>
      </ns0:findCustomerAccountResponse>
   </env:Body>
</env:Envelope>  
  ');
BEGIN
  SELECT EXTRACT(x, '//ns2:AccountNumber/text()', 'xmlns:ns2="http://xmlns.oracle.com/apps/cdm/foundation/parties/customerAccountService/"').getStringVal()
    INTO accountnumber FROM DUAL;
  dbms_output.put_line('accountnumber:'||accountnumber);
  
  SELECT EXTRACT(x, '//ns2:Location/text()', 'xmlns:ns2="http://xmlns.oracle.com/apps/cdm/foundation/parties/customerAccountService/"').getStringVal()
    INTO location FROM DUAL;
  dbms_output.put_line('locations:'||location);
  
  SELECT EXTRACT(x, '//ns2:Location[2]/text()', 'xmlns:ns2="http://xmlns.oracle.com/apps/cdm/foundation/parties/customerAccountService/"').getStringVal()
    INTO location FROM DUAL;
  dbms_output.put_line('location2:'||location);
  
  SELECT EXTRACT(x, '//ns2:Location[3]/text()', 'xmlns:ns2="http://xmlns.oracle.com/apps/cdm/foundation/parties/customerAccountService/"').getStringVal()
    INTO location FROM DUAL;
  dbms_output.put_line('location3:'||location);
END;
/

This will output

accountnumber:XXXXX
locations:XXXXXXXXYYYYYYYY
location2:YYYYYYYY
location3: