Oracle SQL解析SOAP XML请求返回NULL问题求助

问题排查及修正方案 #

核心错误点 #

• 你当前XPath路径里的settleReturn节点未添加pmb命名空间前缀，只要该节点属于http://pmts.ofss.com/ws/PMBookTransferSIService命名空间，就必须加前缀匹配，否则会找不到节点返回空。
• 可新增XMLType转换容错处理，避免CLOB里存在特殊字符、换行、BOM头导致转换失败返回空。
• 日期类型匹配可新增容错规则，适配XML里的日期字符串格式，避免格式不匹配返回NULL。

修正后查询语句 #

select
    a.*
from migt_pm_messages mpm
     , xmltable(
            xmlnamespaces('http://schemas.xmlsoap.org/soap/envelope/' as  "soapenv",
             'http://pmts.ofss.com/ws/PMBookTransferSIService' as "pmb"),
              -- 给settleReturn加上pmb前缀
              '/soapenv:Envelope/soapenv:Body/pmb:settleResponse/pmb:settleReturn'
             -- 新增转换容错，先替换掉CLOB里的BOM头和无效空白
             passing XMLType(REGEXP_REPLACE(mpm.msg, '^[[:space:]]*<', '<'))
             columns
                 TXN_VALUE_DATE date path 'pmb:TXN_VALUE_DATE' default null on conversion error
                 , NETWORK_CODE varchar2(20) path 'pmb:NETWORK_CODE' default null on conversion error
                 , EXCH_RATE number path 'pmb:EXCH_RATE' default null on conversion error
                 , SOURCE_REF_NO varchar2(10) path 'pmb:SOURCE_REF_NO' default null on conversion error
                 , USER_REF_NO varchar2(10) path 'pmb:USER_REF_NO' default null on conversion error
                 , SOURCE_CODE varchar2(10) path 'pmb:SOURCE_CODE' default null on conversion error
                 , TXN_BRANCH varchar2(10) path 'pmb:TXN_BRANCH' default null on conversion error
                 , TXN_BOOKING_DATE date path 'pmb:TXN_BOOKING_DATE' default null on conversion error
                   ) a
where rownum = 1;

额外排查建议 #

如果修改后还是返回空，可先单独执行select XMLType(REGEXP_REPLACE(mpm.msg, '^[[:space:]]*<', '<')) from migt_pm_messages mpm where rownum=1验证CLOB转XML是否正常，确认XML结构和命名空间和你声明的完全一致即可。

内容的提问来源于stack exchange，提问作者Manivannan