SQL Server读取XML数据时为歧义父层级添加唯一标识方案咨询

解决方案 #

可以直接在父节点层级通过XPath定位提取字段，完全不需要生成parentID也不需要后续pivot操作，性能相比原方案提升非常明显。

静态字段提取方案（字段名已知的场景，推荐优先使用） #

直接遍历每一行数据对应的<aggregateddata>父节点，通过XPath筛选指定name属性的<item>节点取value即可：

DECLARE @XmlFile XML = '<?xml version="1.0"?>
<xml>
    <aggregateddata>
        <aggregateddata>
            <item value="abcdefg1" name="id"/>
            <item value="1" name="dataSet"/>
            <item value="Aggregates" name="dataSetLabel"/>
            <item value="Physical Flow" name="indicator"/>
            <item value="day" name="periodType"/>
            <item value="2021-10-16T06:00:00+02:00" name="periodFrom"/>
            <item value="2021-10-17T06:00:00+02:00" name="periodTo"/>
            <item value="BE" name="countryKey"/>
            <item value="L-Zone" name="bzShort"/>
        </aggregateddata>
        <aggregateddata>
            <item value="abcdefg2" name="id"/>
            <item value="1" name="dataSet"/>
            <item value="Aggregates" name="dataSetLabel"/>
            <item value="Physical Flow" name="indicator"/>
            <item value="day" name="periodType"/>
            <item value="2021-10-16T06:00:00+02:00" name="periodFrom"/>
            <item value="2021-10-17T06:00:00+02:00" name="periodTo"/>
            <item value="UK" name="countryKey"/>
            <item value="L-Zone" name="bzShort"/>
        </aggregateddata>
    </aggregateddata>
</xml>'

SELECT
    t.node.value('(item[@name="id"]/@value)[1]', 'VARCHAR(50)') AS id,
    t.node.value('(item[@name="dataSet"]/@value)[1]', 'INT') AS dataSet,
    t.node.value('(item[@name="dataSetLabel"]/@value)[1]', 'VARCHAR(100)') AS dataSetLabel,
    t.node.value('(item[@name="indicator"]/@value)[1]', 'VARCHAR(100)') AS indicator,
    t.node.value('(item[@name="periodType"]/@value)[1]', 'VARCHAR(20)') AS periodType,
    t.node.value('(item[@name="periodFrom"]/@value)[1]', 'DATETIMEOFFSET') AS periodFrom,
    t.node.value('(item[@name="periodTo"]/@value)[1]', 'DATETIMEOFFSET') AS periodTo,
    t.node.value('(item[@name="countryKey"]/@value)[1]', 'VARCHAR(10)') AS countryKey,
    t.node.value('(item[@name="bzShort"]/@value)[1]', 'VARCHAR(50)') AS bzShort
FROM @XmlFile.nodes('/xml/aggregateddata/aggregateddata') AS t(node)

该方案优势：

• 去掉了原方案中O(n²)复杂度的XPath节点计数逻辑，也无需后续pivot操作，整体为线性复杂度，大数据量下性能提升显著
• 可以直接匹配目标表的字段类型定义，无需二次转换
• 缺失字段会自动返回NULL，逻辑清晰可控

动态字段提取方案（字段名未知、需要动态pivot的场景） #

如果字段是动态变化的，需要先提取键值对再做pivot，可以用SQL原生窗口函数生成parentID，性能远好于XPath计数：

WITH parent_nodes AS (
    SELECT 
        ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS parentID,
        t.node.query('.') AS node_content
    FROM @XmlFile.nodes('/xml/aggregateddata/aggregateddata') AS t(node)
)
SELECT
    p.parentID,
    x.item.value('@name', 'VARCHAR(50)') AS FieldName,
    x.item.value('@value', 'VARCHAR(500)') AS FieldValue
FROM parent_nodes p
CROSS APPLY p.node_content.nodes('aggregateddata/item') AS x(item)

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