SQL Server中查询表内多条XML记录里同名节点的指定值

嘿，我来帮你搞定这个XML节点查询的问题！针对你手里的xml_table表——存着unique_id和带多重复节点的xml_data——不同数据库处理XML的方式略有不同，我给你整理了几个主流数据库的实操方案，你按需取用就行：

1. MySQL 8.0+ 解决方案 #

MySQL 8.0及以上版本支持XML_TABLE函数，能直接把XML中的重复节点拆分成行，非常方便：

SELECT 
    t.unique_id,
    line_info.line_num,
    prod_info.prod_name,
    prod_info.uom_weight
FROM 
    xml_table t,
    -- 先拆分出每个ORDER_LINE节点
    XML_TABLE(
        t.xml_data
        COLUMNS 
            line_num INT PATH 'ORDER_LINE/@LINE_NUM',
            prod_node XML PATH 'ORDER_LINE/PROD'
    ) AS line_info,
    -- 再从每个PROD节点中提取具体字段
    XML_TABLE(
        line_info.prod_node
        COLUMNS
            prod_name VARCHAR(50) PATH '@NAME',
            uom_weight DECIMAL(5,2) PATH 'UOM_WEIGHT'
    ) AS prod_info;

2. PostgreSQL 解决方案 #

PostgreSQL的xmltable函数语法更简洁，能一次性提取所有需要的字段：

SELECT 
    t.unique_id,
    order_lines.line_num,
    order_lines.prod_name,
    order_lines.uom_weight
FROM 
    xml_table t,
    xmltable(
        '/ORDER/ORDER_LINE'  -- 指定要遍历的节点路径
        PASSING t.xml_data
        COLUMNS 
            line_num INT PATH '@LINE_NUM',  -- 提取LINE_NUM属性
            prod_name VARCHAR(50) PATH 'PROD/@NAME',  -- 提取PROD的NAME属性
            uom_weight NUMERIC(5,2) PATH 'PROD/UOM_WEIGHT'  -- 提取UOM_WEIGHT元素的值
    ) AS order_lines;

3. SQL Server 解决方案 #

SQL Server用.nodes()方法拆分重复节点，再用.value()提取具体值：

SELECT 
    t.unique_id,
    order_line.value('@LINE_NUM', 'INT') AS line_num,
    order_line.value('PROD/@NAME', 'VARCHAR(50)') AS prod_name,
    order_line.value('PROD/UOM_WEIGHT[1]', 'DECIMAL(5,2)') AS uom_weight
FROM 
    xml_table t
-- 用CROSS APPLY拆分每个ORDER_LINE节点
CROSS APPLY 
    t.xml_data.nodes('/ORDER/ORDER_LINE') AS ord(order_line);

额外提示 #

如果只想筛选特定条件的记录（比如只取产品名为"A"的行），直接在查询末尾加WHERE子句即可，比如：

WHERE prod_name = 'A'

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