Oracle 18c从XML数组提取FieldName=ACTIVITY的特定节点值

Oracle 18c环境

测试场景1 #

通过视图sde.gdb_items_vw查询CLOB类型存储的XML内容时，使用xmltype做类型转换、xmlsequence拆分/DETableInfo/Subtypes/Subtype路径节点、extractvalue提取SubtypeCode、SubtypeName、FieldName、DomainName值。当<FieldInfos>数组仅包含FieldName=ACTIVITY的单条SubtypeFieldInfo记录时，查询可正常返回subtype_code、subtype_name、field_name、domain_name结果。

测试场景2 #

当<FieldInfos>数组同时包含FieldName=ACTIVITY、FieldName=STRATEGY的多条SubtypeFieldInfo记录时，执行相同查询触发ORA-19025错误：EXTRACTVALUE returns value of only one node，根因是XPath匹配到多个节点，而EXTRACTVALUE仅支持返回单个节点值。

核心调整逻辑是在XPath中增加节点谓词筛选，直接定位到FieldName=ACTIVITY对应的目标节点，避免全量匹配所有SubtypeFieldInfo节点触发多节点报错，提供两种可直接落地的写法：

• 写法1：最小改动适配原有extractvalue逻辑
  仅需修改提取FieldName、DomainName字段的XPath路径，增加[FieldName="ACTIVITY"]筛选条件，其他逻辑保持不变，示例代码如下：

  SELECT
    extractvalue(VALUE(s), '/Subtype/SubtypeCode') AS subtype_code,
    extractvalue(VALUE(s), '/Subtype/SubtypeName') AS subtype_name,
    -- XPath增加谓词，仅匹配FieldName为ACTIVITY的节点
    extractvalue(VALUE(s), '/Subtype/FieldInfos/SubtypeFieldInfo[FieldName="ACTIVITY"]/FieldName') AS field_name,
    extractvalue(VALUE(s), '/Subtype/FieldInfos/SubtypeFieldInfo[FieldName="ACTIVITY"]/DomainName') AS domain_name
  FROM
    sde.gdb_items_vw i,
    TABLE(xmlsequence(xmltype(i.definition).extract('/DETableInfo/Subtypes/Subtype'))) s
  WHERE
    -- 替换为实际业务过滤条件，比如指定要素类/表名
    i.name = 'YOUR_TARGET_TABLE_NAME';
  

• 写法2：XMLTABLE拆分写法（推荐）
  Oracle 18c中EXTRACTVALUE已被标记为废弃，优先使用XMLTABLE做XML节点解析，可先将Subtype下的所有SubtypeFieldInfo拆分为独立行，再通过普通WHERE条件过滤FieldName=ACTIVITY的记录，扩展性更强，后续需要提取其他FieldName对应的域值时无需修改XPath结构，示例代码如下：

  SELECT
    s.subtype_code,
    s.subtype_name,
    f.field_name,
    f.domain_name
  FROM
    sde.gdb_items_vw i,
    XMLTABLE(
      '/DETableInfo/Subtypes/Subtype'
      PASSING xmltype(i.definition)
      COLUMNS
        subtype_code   NUMBER        PATH 'SubtypeCode',
        subtype_name   VARCHAR2(200) PATH 'SubtypeName',
        field_info_col XMLTYPE       PATH 'FieldInfos/SubtypeFieldInfo'
    ) s,
    XMLTABLE(
      '/SubtypeFieldInfo'
      PASSING s.field_info_col
      COLUMNS
        field_name  VARCHAR2(100) PATH 'FieldName',
        domain_name VARCHAR2(200) PATH 'DomainName'
    ) f
  WHERE
    f.field_name = 'ACTIVITY'
    -- 替换为实际业务过滤条件
    AND i.name = 'YOUR_TARGET_TABLE_NAME';
  

注意：XPath中字符串匹配区分大小写，需保证筛选值ACTIVITY和XML中存储的FieldName值大小写完全一致，否则会出现匹配不到节点返回空值的问题。

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