Oracle中捕获XMLTABLE()错误定位异常XML值的实现方法

• 运行环境为Oracle 18c，使用地理数据库内置视图GDB_ITEMS_VW，视图中CLOB类型的definition列存储XML格式的编码值域定义数据。
• 初始编写的XML解析查询可正常提取值域的编码、描述、域名称信息，SQL如下：

select      
    x.code,
    x.description,
    i.name as domain_name
from        
    sde.gdb_items_vw i
cross apply xmltable(
    '/GPCodedValueDomain2/CodedValues/CodedValue' 
    passing xmltype(i.definition)
    columns
        code        varchar2(255) path './Code',
        description varchar2(255) path './Name'
    ) x  

• SQL Developer默认仅返回前50行查询结果时，上述语句运行无报错；拉取全量结果时触发XML解析错误，错误信息如下：

ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00007: unexpected end-of-file encountered
ORA-06512: at "SYS.XMLTYPE", line 272
ORA-06512: at line 1
31011. 00000 -  "XML parsing failed"
*Cause:    XML parser returned an error while trying to parse the document.
*Action:   Check if the document to be parsed is valid.

• 最初尝试参考异常捕获思路，在WITH子句中编写临时函数筛选解析失败的行，运行时抛出ORA-00905: missing keyword错误，需要正确实现问题行定位逻辑。

之前编写的代码存在三个核心问题：

1. 语法逻辑错误：XMLTABLE返回的是多行结果集，不能直接赋值给单个XMLTYPE类型的变量，赋值类型不匹配本身就会触发语法报错。
2. 触发点判断错误：解析失败发生在XMLTYPE(v_xml)构造XML对象的阶段，不是XMLTABLE遍历节点的阶段，不需要在校验函数中调用XMLTABLE。
3. 配置问题：Oracle的WITH子句内定义PL/SQL函数是12c之后推出的特性，部分客户端默认关闭该特性支持，会直接报关键字缺失错误。

方案1：创建持久化校验函数（兼容性最好，无客户端配置要求） #

直接在数据库中创建独立的校验函数，所有客户端连接都可以直接调用，不需要调整配置：

CREATE OR REPLACE FUNCTION is_valid_xml(p_xml CLOB) RETURN NUMBER
IS
  v_temp_xml XMLTYPE;
BEGIN
  -- 仅尝试将CLOB转为XML类型，成功则返回1，异常则返回0
  v_temp_xml := XMLTYPE(p_xml);
  RETURN 1;
EXCEPTION
  WHEN OTHERS THEN
    RETURN 0;
END;
/

函数创建完成后，执行如下查询即可筛选出所有XML解析异常的行，同时返回异常XML的前4000个字符方便排查问题：

SELECT
  i.name AS domain_name,
  DBMS_LOB.SUBSTR(i.definition, 4000, 1) AS invalid_xml_content_preview
FROM sde.gdb_items_vw i
WHERE is_valid_xml(i.definition) = 0;

方案2：使用WITH临时函数（无需创建持久化对象，需调整客户端配置） #

如果没有创建数据库函数的权限，可以用WITH临时函数实现，首先需要在SQL Developer中开启对应特性：

• 打开菜单栏「工具」→「首选项」→「数据库」→「高级」
• 勾选「启用WITH子句中的PL/SQL」选项（不同版本名称略有差异，对应英文选项为Enable PL/SQL in WITH clause），保存配置后重连数据库。
  之后执行修正后的查询语句即可：

WITH FUNCTION is_valid_xml(p_xml CLOB) RETURN NUMBER
IS
  v_temp_xml XMLTYPE;
BEGIN
  v_temp_xml := XMLTYPE(p_xml);
  RETURN 1;
EXCEPTION
  WHEN OTHERS THEN
    RETURN 0;
END;
SELECT
  i.name AS domain_name,
  DBMS_LOB.SUBSTR(i.definition, 4000, 1) AS invalid_xml_content_preview
FROM sde.gdb_items_vw i
WHERE is_valid_xml(i.definition) = 0;

查到异常行后，查看预览的XML内容即可定位问题：LPX-00007: unexpected end-of-file encountered错误基本都是XML内容被截断、结束标签缺失、特殊字符未转义导致的，修正对应definition字段的XML内容后，原全量解析查询即可正常运行。

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