Oracle XMLTABLE查询XML无结果 无法获取exceptionAppCode等字段值

问题原因 #

你遇到的无返回问题由两个错误共同导致：

• XML根节点定义了默认命名空间http://test.com/test/services/test/v1，但你调用XMLTABLE时没有声明对应命名空间，XPath路径无法匹配到对应节点
• 目标字段exceptionAppCode、exceptionAppMessage是MessageFault节点下appDetail子节点的子属性，你写的字段路径遗漏了appDetail/层级，就算命名空间配置正确也无法提取到值

修正后的查询语句 #

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(xmlnamespaces(default 'http://test.com/test/services/test/v1'),
                  '/stateChangeEventRequest/MessageFault'
                  PASSING a.xml COLUMNS 
                  exceptionAppCode varchar2(64) path 'appDetail/exceptionAppCode',
                  exceptionAppMessage varchar2(512) path 'appDetail/exceptionAppMessage') xml_value;

修改说明 #

• 新增了xmlnamespaces(default 'http://test.com/test/services/test/v1')配置，声明默认命名空间和XML定义保持一致
• 两个目标字段的path路径新增appDetail/前缀，匹配实际的节点层级

内容的提问来源于stack exchange，提问作者Gustavo J. Moretti