Oracle XMLTABLE issue

I am trying to get the values of exceptionAppCode and exceptionAppMessage from the below XML through this script, but nothing comes out. Can anyone have any idea?

SELECT *
FROM   (SELECT xmltype ('<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<stateChangeEventRequest
    xmlns="http://test.com/test/services/test/v1">
    <ServiceOrder>
        <ID>666</ID>
        <interactionDate>2021-04-28T15:55:50.761-05:00</interactionDate>
        <interactionDateComplete>2021-04-28T15:55:49.233-05:00</interactionDateComplete>
        <interactionStatus>FAILED</interactionStatus>
        <externalId>666-666</externalId>
        <action>TEST</action>
        <orderType>CREATE</orderType>
    </ServiceOrder>
    <MessageFault>
        <exceptionCategory>XPTO</exceptionCategory>
        <exceptionCode>1050</exceptionCode>
        <exceptionMessage>Error</exceptionMessage>
        <exceptionDetail>createXXX</exceptionDetail>
        <exceptionSeverity>E</exceptionSeverity>
        <exceptionType>String</exceptionType>
        <appDetail>
            <exceptionAppCode>666</exceptionAppCode>
            <exceptionAppMessage>There is no one who loves pain itself, who seeks after it and wants to have it, simply because it is pain</exceptionAppMessage>
        </appDetail>
    </MessageFault>
</stateChangeEventRequest>') AS xml FROM DUAL) a,
         xmltable('/stateChangeEventRequest/MessageFault'
                  PASSING a.xml COLUMNS 
                  exceptionAppCode varchar2(64) path 'exceptionAppCode',
                  exceptionAppMessage varchar2(512) path 'exceptionAppMessage') xml_value;

My sample xml is :

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<stateChangeEventRequest
    xmlns="http://test.com/test/services/test/v1">
    <ServiceOrder>
        <ID>666</ID>
        <interactionDate>2021-04-28T15:55:50.761-05:00</interactionDate>
        <interactionDateComplete>2021-04-28T15:55:49.233-05:00</interactionDateComplete>
        <interactionStatus>FAILED</interactionStatus>
        <externalId>666-666</externalId>
        <action>TEST</action>
        <orderType>CREATE</orderType>
    </ServiceOrder>
    <MessageFault>
        <exceptionCategory>XPTO</exceptionCategory>
        <exceptionCode>1050</exceptionCode>
        <exceptionMessage>Error</exceptionMessage>
        <exceptionDetail>createXXX</exceptionDetail>
        <exceptionSeverity>E</exceptionSeverity>
        <exceptionType>String</exceptionType>
        <appDetail>
            <exceptionAppCode>666</exceptionAppCode>
            <exceptionAppMessage>There is no one who loves pain itself, who seeks after it and wants to have it, simply because it is pain</exceptionAppMessage>
        </appDetail>
    </MessageFault>
</stateChangeEventRequest>

I'm not sure if I need to set the xmlnamespace.

Solution

Please try the following solution.

SQL

with tbl as
(
    select
        XMLType(
        '<stateChangeEventRequest xmlns="http://test.com/test/services/test/v1">
    <ServiceOrder>
        <ID>666</ID>
        <interactionDate>2021-04-28T15:55:50.761-05:00</interactionDate>
        <interactionDateComplete>2021-04-28T15:55:49.233-05:00</interactionDateComplete>
        <interactionStatus>FAILED</interactionStatus>
        <externalId>666-666</externalId>
        <action>TEST</action>
        <orderType>CREATE</orderType>
    </ServiceOrder>
    <MessageFault>
        <exceptionCategory>XPTO</exceptionCategory>
        <exceptionCode>1050</exceptionCode>
        <exceptionMessage>Error</exceptionMessage>
        <exceptionDetail>createXXX</exceptionDetail>
        <exceptionSeverity>E</exceptionSeverity>
        <exceptionType>String</exceptionType>
        <appDetail>
            <exceptionAppCode>666</exceptionAppCode>
            <exceptionAppMessage>There is no one who loves pain itself, who seeks after it and wants to have it, simply because it is pain</exceptionAppMessage>
        </appDetail>
    </MessageFault>
</stateChangeEventRequest>'
        ) xmldata
    from dual
)
select exceptionAppCode, exceptionAppMessage
from   tbl,
       xmltable(
         xmlnamespaces(default 'http://test.com/test/services/test/v1'),
         '/stateChangeEventRequest/MessageFault/appDetail'
         PASSING tbl.xmldata 
         COLUMNS exceptionAppCode VARCHAR2(10) PATH 'exceptionAppCode',
              exceptionAppMessage VARCHAR2(1024) PATH 'exceptionAppMessage'
         );

Output

+-------------------+-----------------------------------------------------------------------------------------------------------+
| EXCEPTIONAPPCODE  |                                            EXCEPTIONAPPMESSAGE                                            |
+-------------------+-----------------------------------------------------------------------------------------------------------+
|              666  | There is no one who loves pain itself, who seeks after it and wants to have it, simply because it is pain |
+-------------------+-----------------------------------------------------------------------------------------------------------+