Oracle XMLTABLE问题

Question

我试图通过这个脚本从下面的XML中获取exceptionAppCode和exceptionAppMessage的值，但是什么也没有出来。有人知道吗？

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;

我的示例xml是：

<?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>

我不确定是否需要设置the命名空间。

Answer 1

请尝试以下解决方案。

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'
         );

输出

+-------------------+-----------------------------------------------------------------------------------------------------------+
| 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 |
+-------------------+-----------------------------------------------------------------------------------------------------------+