How to parse XML by xmltype in Oracle

Question

I have a little problem here with the XPath property when I'm trying to parse a XML document. This is my example:

DECLARE
   px_return    XMLTYPE
      := XMLTYPE (
            '<?xml version="1.0" encoding="UTF-8"?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
    <SOAP:Header xmlns:SOAP="http://schemas.xmlsoap.org/soap/envelope/">
        <h:AxisValues xmlns:s="http://schemas.xmlsoap.org/soap/envelope/" xmlns:h="urn:/microsoft/multichannelframework/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:/microsoft/multichannelframework/">
            <User xmlns="">FSCD</User>
            <Solution xmlns="">Multicare</Solution>
            <ApplicationalUser xmlns=""/>
            <ApplicationalUserSystem xmlns=""/>
            <SystemUser xmlns=""/>
            <SystemUserSystem xmlns=""/>
            <Proxy xmlns="">0</Proxy>
        </h:AxisValues>
    </SOAP:Header>
    <SOAP:Body xmlns:SOAP="http://schemas.xmlsoap.org/soap/envelope/">
        <ns1:maintainMandateResponse xmlns:ns1="urn:enterprise.com/ws/SAP/Finantial/MaintainMandate/V1">
            <return>
                <messageType>E</messageType>
            </return>
        </ns1:maintainMandateResponse>
    </SOAP:Body>
</soapenv:Envelope>');

   lv_msgType   VARCHAR2 (20);
BEGIN
   SELECT Return.msgType
     INTO lv_msgType
     FROM XMLTABLE (
             xmlnamespaces (
                DEFAULT 'enterprise.com/ws/SAP/Finantial/MaintainMandate/V1',
                'http://schemas.xmlsoap.org/soap/envelope/' AS "soapenv",
                'http://schemas.xmlsoap.org/soap/envelope/' AS "SOAP",
                'enterprise.com/ws/SAP/Finantial/MaintainMandate/V1' AS "ns1"),
             '//soapenv:Envelope/SOAP:Body/ns1:maintainMandateResponse'
             PASSING px_return
             COLUMNS msgType VARCHAR2 (1) PATH 'messageType') Return;


   DBMS_OUTPUT.put_line ('Message type: ' || lv_msgType);
END;

I'm getting a NO_DATA_FOUND exception because I can't find results in this parsing method.

I've tried a lot of different strategies, included putting return in the PATH or in the XQUery string but with no success.

I think this is a small and simple problem but I am not able to find. Thanks in advance! Filipe

Alex Poole · Accepted Answer · 2016-03-02 09:28:20Z

1

You are missing the urn: prefix in your ns1 namespace declaration. You are also ignoring the <return> node level, and you have a default namespace which is incorrect as you have child nodes without any namespace. So you need:

   SELECT Return.msgType
     INTO lv_msgType
     FROM XMLTABLE (
             xmlnamespaces (
                'http://schemas.xmlsoap.org/soap/envelope/' AS "soapenv",
                'http://schemas.xmlsoap.org/soap/envelope/' AS "SOAP",
                'urn:enterprise.com/ws/SAP/Finantial/MaintainMandate/V1' AS "ns1"),
             '/soapenv:Envelope/SOAP:Body/ns1:maintainMandateResponse'
             PASSING px_return
             COLUMNS msgType VARCHAR2 (1) PATH 'return/messageType') Return;

Which gets:

PL/SQL procedure successfully completed.

Message type: E

Or you can also move the return into the XPath of course, which has the same effect here:

             '/soapenv:Envelope/SOAP:Body/ns1:maintainMandateResponse/return'
             PASSING px_return
             COLUMNS msgType VARCHAR2 (1) PATH 'messageType') Return;

answered Mar 2, 2016 at 9:28

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

milheiros Over a year ago

Thanks Alex. It was obviously a small path problem.

Collectives™ on Stack Overflow

How to parse XML by xmltype in Oracle

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related