SQL Server中XML批量插入：获取期望的列格式结果

核心思路 #

XML中每个<object>节点对应一行数据，内部<parameter>节点是列名与值的键值对。我们需要先提取所有唯一列名，再通过动态PIVOT将行数据转为标准列格式，最后批量插入目标表。

步骤1：加载XML并提取所有唯一参数名（列名） #

读取XML文件，收集所有<parameter>的name属性作为后续列名：

DECLARE @xml xml
SELECT @xml = C FROM OPENROWSET (BULK 'E:\Cell_Sense\CM_Input\my_xml.xml', SINGLE_BLOB) AS Cars(C)

-- 收集所有唯一参数名（SQL Server 2017+ 适用）
DECLARE @cols NVARCHAR(MAX)
SELECT @cols = STRING_AGG(QUOTENAME(param.value('@name', 'varchar(100)')), ', ')
FROM @xml.nodes('/bulkCmConfigDataFile/configData/class/object/class/object/parameter') AS PARAM(param)
GROUP BY param.value('@name', 'varchar(100)')

-- 若使用SQL Server 2016及以下版本，替换为以下代码拼接列名
-- SELECT @cols = STUFF((
--     SELECT DISTINCT ', ' + QUOTENAME(param.value('@name', 'varchar(100)'))
--     FROM @xml.nodes('/bulkCmConfigDataFile/configData/class/object/class/object/parameter') AS PARAM(param)
--     FOR XML PATH(''), TYPE
-- ).value('.', 'NVARCHAR(MAX)'), 1, 2, '')

步骤2：生成动态PIVOT SQL并转换为列格式 #

通过动态SQL构建PIVOT语句，将每个<object>节点转为一行标准列数据：

DECLARE @sql NVARCHAR(MAX)
SET @sql = N'
SELECT ' + @cols + N'
FROM (
    SELECT
        -- 用generate-id确保每个object节点唯一标识，避免PIVOT时数据混乱
        obj.query(''generate-id(.)'').value(''.'', ''varchar(100)'') AS ObjectID,
        param.value(''@name'', ''varchar(100)'') AS ParamName,
        param.value(''@value'', ''varchar(100)'') AS ParamValue
    FROM @xml.nodes(''/bulkCmConfigDataFile/configData/class/object/class/object'') AS OBJ(obj)
    CROSS APPLY obj.nodes(''parameter'') AS PARAM(param)
) AS src
PIVOT (
    MAX(ParamValue)
    FOR ParamName IN (' + @cols + N')
) AS pvt'

-- 执行动态SQL并传递XML参数
EXEC sp_executesql @sql, N'@xml xml', @xml = @xml

步骤3：批量插入目标表 #

将动态SQL中的SELECT替换为INSERT INTO语句（需确保目标表列名与XML参数名匹配）：

SET @sql = N'
INSERT INTO 你的目标表名 (' + @cols + N')
SELECT ' + @cols + N'
FROM (
    SELECT
        obj.query(''generate-id(.)'').value(''.'', ''varchar(100)'') AS ObjectID,
        param.value(''@name'', ''varchar(100)'') AS ParamName,
        param.value(''@value'', ''varchar(100)'') AS ParamValue
    FROM @xml.nodes(''/bulkCmConfigDataFile/configData/class/object/class/object'') AS OBJ(obj)
    CROSS APPLY obj.nodes(''parameter'') AS PARAM(param)
) AS src
PIVOT (
    MAX(ParamValue)
    FOR ParamName IN (' + @cols + N')
) AS pvt'

EXEC sp_executesql @sql, N'@xml xml', @xml = @xml

关键说明 #

• 替换旧方法：使用XQuery的nodes和CROSS APPLY比传统OPENXML更高效，尤其适合大体积XML，避免资源开销。
• 自动适配未知列：动态收集参数名，无需提前知晓所有列名，适配XML结构变化。
• 空值处理：未包含的参数自动填充NULL，符合关系表空值逻辑。

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