Oracle中如何从单个xmltype标签提取值拆分到不同列

问题原因 #

你当前使用的extract函数匹配到单条XML中多个<LOCATION>节点时，getStringVal()会自动将所有匹配到的TYPE属性值拼接返回，因此会出现ABSABSREL这类合并结果。

方案1：拆分为独立行（节点数量不固定时推荐） #

使用Oracle自带的XMLTable函数拆分每个<LOCATION>节点，每个节点生成单独的行记录，同时还可同步提取其他属性/坐标值，适合后续存入表中：

SELECT 
    a.object_id,
    x.location_type AS "Location Type",
    x.epsg AS "EPSG编码",
    x.x_val AS "坐标X",
    x.y_val AS "坐标Y"
FROM 
    object_history a,
    XMLTable(
        '//LOCATION' 
        PASSING a.xml_data
        COLUMNS 
            location_type VARCHAR2(10) PATH '@TYPE',
            epsg VARCHAR2(10) PATH '@EPSG',
            x_val NUMBER PATH 'X',
            y_val NUMBER PATH 'Y'
    ) x;

如果需要直接存入新表，在查询前加CREATE TABLE 你的新表名 AS即可。

方案2：拆分为固定独立列（节点数量固定时使用） #

如果明确知道单条XML中最多有N个<LOCATION>节点，可以指定节点位置分别提取到独立列：

SELECT 
    a.object_id,
    a.xml_data.extract('//LOCATION[1]/@TYPE').getStringVal() AS "Location Type 1",
    a.xml_data.extract('//LOCATION[2]/@TYPE').getStringVal() AS "Location Type 2",
    a.xml_data.extract('//LOCATION[3]/@TYPE').getStringVal() AS "Location Type 3"
    -- 有更多节点继续按序号加即可
FROM 
    object_history a;

如果要写入原表的新增列，先执行ALTER TABLE给object_history加对应字段，再用上述查询结果做UPDATE批量赋值即可。

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