如何从Oracle表中提取XML中非零NUM值的QTY标签数据？

要实现从XML中提取NUM非零的QTY数据并转为动态列，可通过**XPath过滤+XMLTable解析+ pivot（静态/动态）**的组合方案完成，以下是具体实现：

核心思路 #

1. XPath直接过滤：用XPath表达式筛选出NUM值不为0的QTY节点，避免后续处理无效数据
2. XMLTable解析：将XML节点转换为关系型行数据，提取TYPE、NUM、Currency等字段
3. 列转换：通过静态CASE语句（固定最大列数）或动态SQL（自适应列数）将行数据转为列

1. 静态列方案（适用于已知最大非零QTY数量） #

假设你的表名为trades，包含trade_id（交易ID）和xml_data（XML字段），以下查询可提取最多4组非零QTY数据：

WITH non_zero_fees AS (
    SELECT 
        t.trade_id,
        x.qty_type,
        x.num_value,
        x.currency,
        x.seq_num
    FROM trades t,
    XMLTable(
        -- XPath过滤NUM非零的QTY节点，number()转换为数值比较更严谨
        '/TRADE/QTYS/QTY[number(NUM) != 0]'
        PASSING t.xml_data
        COLUMNS
            qty_type VARCHAR2(50) PATH '@TYPE', -- 提取QTY的TYPE属性
            num_value NUMBER PATH 'NUM', -- 提取NUM值
            currency VARCHAR2(3) PATH 'ID', -- 提取货币类型
            seq_num NUMBER PATH 'position()' -- 生成非零QTY的顺序编号
    ) x
)
SELECT 
    trade_id,
    -- 第一组非零费用
    MAX(CASE WHEN seq_num = 1 THEN qty_type END) AS FEE_1_TYPE,
    MAX(CASE WHEN seq_num = 1 THEN num_value END) AS FEE_1_NUM,
    MAX(CASE WHEN seq_num = 1 THEN currency END) AS FEE_1_CURRENCY,
    -- 第二组非零费用
    MAX(CASE WHEN seq_num = 2 THEN qty_type END) AS FEE_2_TYPE,
    MAX(CASE WHEN seq_num = 2 THEN num_value END) AS FEE_2_NUM,
    MAX(CASE WHEN seq_num = 2 THEN currency END) AS FEE_2_CURRENCY,
    -- 第三组非零费用
    MAX(CASE WHEN seq_num = 3 THEN qty_type END) AS FEE_3_TYPE,
    MAX(CASE WHEN seq_num = 3 THEN num_value END) AS FEE_3_NUM,
    MAX(CASE WHEN seq_num = 3 THEN currency END) AS FEE_3_CURRENCY,
    -- 第四组非零费用
    MAX(CASE WHEN seq_num = 4 THEN qty_type END) AS FEE_4_TYPE,
    MAX(CASE WHEN seq_num = 4 THEN num_value END) AS FEE_4_NUM,
    MAX(CASE WHEN seq_num = 4 THEN currency END) AS FEE_4_CURRENCY
FROM non_zero_fees
GROUP BY trade_id;

说明 #

• XPath表达式/TRADE/QTYS/QTY[number(NUM) != 0]直接过滤掉NUM为0的QTY节点
• position()函数生成非零QTY的顺序编号，保证列顺序与XML中节点顺序一致
• 若交易的非零QTY数量少于4，对应列会显示NULL

2. 动态列方案（自适应非零QTY数量） #

如果需要根据数据自动调整列数，可通过PL/SQL生成动态SQL：

DECLARE
    max_seq NUMBER; -- 记录所有交易中最多的非零QTY数量
    sql_stmt VARCHAR2(4000); -- 动态SQL语句
BEGIN
    -- 1. 获取最大非零QTY数量
    SELECT COALESCE(MAX(seq_num), 0) INTO max_seq
    FROM (
        SELECT x.seq_num
        FROM trades t,
        XMLTable(
            '/TRADE/QTYS/QTY[number(NUM) != 0]'
            PASSING t.xml_data
            COLUMNS seq_num NUMBER PATH 'position()'
        ) x
    );

    -- 2. 构建动态SQL
    sql_stmt := 'WITH non_zero_fees AS (
        SELECT 
            t.trade_id,
            x.qty_type,
            x.num_value,
            x.currency,
            x.seq_num
        FROM trades t,
        XMLTable(
            ''/TRADE/QTYS/QTY[number(NUM) != 0]''
            PASSING t.xml_data
            COLUMNS
                qty_type VARCHAR2(50) PATH ''@TYPE'',
                num_value NUMBER PATH ''NUM'',
                currency VARCHAR2(3) PATH ''ID'',
                seq_num NUMBER PATH ''position()''
        ) x
    )
    SELECT trade_id';

    -- 循环生成对应数量的列
    FOR i IN 1..max_seq LOOP
        sql_stmt := sql_stmt || ',
            MAX(CASE WHEN seq_num = ' || i || ' THEN qty_type END) AS FEE_' || i || '_TYPE,
            MAX(CASE WHEN seq_num = ' || i || ' THEN num_value END) AS FEE_' || i || '_NUM,
            MAX(CASE WHEN seq_num = ' || i || ' THEN currency END) AS FEE_' || i || '_CURRENCY';
    END LOOP;

    sql_stmt := sql_stmt || '
    FROM non_zero_fees
    GROUP BY trade_id';

    -- 3. 执行动态SQL
    EXECUTE IMMEDIATE sql_stmt;
END;
/

说明 #

• 先统计所有交易中最多的非零QTY数量，再生成对应数量的列
• 最终结果列数完全匹配数据中的非零QTY数量，无多余空列

关键注意点 #

• 避免使用已废弃的extractvalue函数，推荐使用XMLTable进行XML解析
• XPath中用number(NUM)转换为数值比较，可兼容0、0.0、0.0000等多种格式的零值
• 若需要保留XML中QTY的原始顺序，使用position()生成编号即可

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