在Oracle中解析嵌套XML以获取属性值

针对存储在Oracle表列中的嵌套XML，可通过Oracle原生XML处理函数（XMLTYPE、XMLTABLE、XMLQUERY）遍历行并提取目标属性值。以下是适配你提供的XML结构的解决方案：

核心SQL示例 #

假设你的表名为your_table，存储XML的列名为xml_data，执行以下SQL可提取每个列表条目的name、description、isGenerated、objectId、stamp等属性：

SELECT
    t.row_id, -- 替换为你的表主键或标识列
    x.entry_name,
    x.entry_description,
    x.is_generated,
    x.object_id,
    x.stamp,
    x.is_invalid,
    x.reason_english,
    x.reason_local
FROM
    your_table t,
    XMLTABLE(
        '//property[@name="list"]/object[@type="ObjectList:entry"]'
        PASSING XMLTYPE(t.xml_data)
        COLUMNS
            entry_name VARCHAR2(100) PATH './property[@name="name"]/@value',
            entry_description VARCHAR2(200) PATH './property[@name="description"]/@value',
            is_generated VARCHAR2(10) PATH './property[@name="isGenerated"]/@value',
            object_id VARCHAR2(50) PATH './property[@name="objectId"]/@value',
            stamp VARCHAR2(100) PATH './property[@name="stamp"]/@value',
            is_invalid VARCHAR2(10) PATH './property[@name="isInvalid"]/@value',
            reason_english VARCHAR2(200) PATH './property[@name="Reason"]/object/property[@name="messageEnglish"]/@value',
            reason_local VARCHAR2(200) PATH './property[@name="Reason"]/object/property[@name="messageLocal"]/@value'
    ) x;

代码说明 #

1. XMLTYPE转换：将表中字符串类型的XML列转换为Oracle可识别的XML类型对象。
2. XMLTABLE定位节点：通过XPath表达式//property[@name="list"]/object[@type="ObjectList:entry"]定位所有列表条目节点。
3. COLUMNS映射属性：每个字段通过XPath指定对应属性的路径，提取@value属性值；可选属性（如isInvalid、Reason）若不存在会返回NULL，可通过COALESCE设置默认值适配业务需求。
4. 关联表数据：通过交叉连接将原表行与解析出的列表条目一一对应。

适配调整 #

• 替换row_id为你的表实际主键或标识列名。
• 根据属性值的实际长度调整VARCHAR2的参数长度。
• 如需过滤特定条目，可在WHERE子句中添加条件（如x.is_invalid = 'true'）。

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