如何在MSSQL中将多个XML按测试名称解析到独立表中

需求说明 #

已将多个XML文件上传至MSSQL数据库，需要按Test name将数据解析到独立数据表，每个表结构要求：

• 首列：datetime（来自<eolpresetting>下的<datetime>节点）
• 第二列：order id（来自<eolpresetting>下<order>节点的id属性）
• 其余列：对应每个测试名称下的各value值（比如Time测试下的Time of start、Time of save、Time作为列）

同时希望了解是否有更简便的方式，比如从Windows文件夹导入时直接完成解析。目前尝试编写SQL查询但无法正确实现需求。

XML示例 #

<eolresult>
  <eolpresetting>
    <datetime>17.02.2022 16:10:37</datetime>
    <order id="16HBOD0222XSK316_FLX101xxxxxxxxxxxxxx000SK35X3PN9P40006C24A4NORL0L7P1XVV0008I06T24D0XVU0006A0LTS000A" />
  </eolpresetting>
  <testresult>IO</testresult>
  <results>
    <!--PRN="16HBOD0222XSK316_FLX101xxxxxxxxxxxxxx000SK35X3PN9P40006C24A4NORL0L7P1XVV0008I06T24D0XVU0006A0LTS000A"-->
    <test name="Time">
      <value name="Time of start ">17.02.2022 16:08:08</value>
      <value name="Time of save ">17.02.2022 16:10:37</value>
      <value name="Time ">149 s</value>
    </test>
    <!-- 省略其余test节点 -->
  </results>
</eolresult>

尝试的SQL代码 #

USE EOL_Testers
GO

DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)

SELECT @XML = XMLData FROM XMLFilesTable

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

SELECT datetime, order id, test
FROM OPENXML(@hDoc, 'eolresult/eolpresetting/results/test')
WITH 
(
datetime [varchar](150) '@datetime',
order id [varchar](150) '@orderid',
datetime [varchar](150) '@datetime'
)

EXEC sp_xml_removedocument @hDoc
GO

解决方案 #

1. 修正基础解析逻辑（针对单个XML） #

现有SQL存在路径错误、列重复定义、属性映射错误等问题，改用现代XQuery语法替代OPENXML，先获取结构化的基础数据：

USE EOL_Testers
GO

SELECT
    -- 提取datetime节点值
    x.eol.value('(eolpresetting/datetime/text())[1]', 'DATETIME') AS [datetime],
    -- 提取order节点的id属性
    x.eol.value('(eolpresetting/order/@id)[1]', 'VARCHAR(255)') AS [order_id],
    -- 提取测试名称
    t.test.value('@name', 'VARCHAR(100)') AS [test_name],
    -- 提取value的名称和对应值
    v.val.value('@name', 'VARCHAR(100)') AS [value_name],
    v.val.value('text()[1]', 'VARCHAR(100)') AS [value]
FROM XMLFilesTable
CROSS APPLY XMLData.nodes('/eolresult') AS x(eol)
CROSS APPLY x.eol.nodes('results/test') AS t(test)
CROSS APPLY t.test.nodes('value') AS v(val)

2. 动态生成测试专属数据表并插入数据 #

由于不同测试的value列可能不同，需要动态生成表结构和插入语句：

USE EOL_Testers
GO

DECLARE @DynamicSQL NVARCHAR(MAX) = ''

-- 按测试名称分组，生成创建表和插入数据的SQL
SELECT @DynamicSQL = @DynamicSQL + '
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = ''' + REPLACE(t.test.value('@name', 'VARCHAR(100)'), '''', '''''') + ''')
BEGIN
    CREATE TABLE ' + QUOTENAME(t.test.value('@name', 'VARCHAR(100)')) + ' (
        [datetime] DATETIME,
        [order_id] VARCHAR(255),
        ' + STRING_AGG(QUOTENAME(v.val.value('@name', 'VARCHAR(100)')) + ' VARCHAR(100)', ', ') + '
    )
END

INSERT INTO ' + QUOTENAME(t.test.value('@name', 'VARCHAR(100)')) + '
SELECT
    base.[datetime],
    base.[order_id],
    ' + STRING_AGG(QUOTENAME(v.val.value('@name', 'VARCHAR(100)')), ', ') + '
FROM (
    SELECT
        x.eol.value(''(eolpresetting/datetime/text())[1]'', ''DATETIME'') AS [datetime],
        x.eol.value(''(eolpresetting/order/@id)[1]'', ''VARCHAR(255)'') AS [order_id],
        t_inner.test.query(''.'') AS test_node
    FROM XMLFilesTable
    CROSS APPLY XMLData.nodes(''/eolresult'') AS x(eol)
    CROSS APPLY x.eol.nodes(''results/test[@name='''' + ''''' + REPLACE(t.test.value('@name', 'VARCHAR(100)'), '''', '''''') + ''''' + '''']'') AS t_inner(test)
) AS base
CROSS APPLY base.test_node.nodes(''test/value'') AS v_inner(val)
PIVOT (
    MAX(v_inner.val.value(''text()[1]'', ''VARCHAR(100)''))
    FOR v_inner.val.value(''@name'', ''VARCHAR(100)'') IN (' + STRING_AGG(QUOTENAME(v.val.value('@name', 'VARCHAR(100)')), ', ') + ')
) AS pvt
'
FROM XMLFilesTable
CROSS APPLY XMLData.nodes('/eolresult/results/test') AS t(test)
CROSS APPLY t.test.nodes('value') AS v(val)
GROUP BY t.test.value('@name', 'VARCHAR(100)')

-- 执行动态生成的SQL
EXEC sp_executesql @DynamicSQL

3. 更简便的导入解析方式 #

方式一：SSIS批量导入解析

使用SQL Server Integration Services（SSIS）可以实现从文件夹直接导入并解析：

• 用Foreach Loop Container遍历目标文件夹中的所有XML文件
• 用XML Source组件读取XML内容，配置节点路径或关联XSD架构
• 添加Conditional Split组件按test_name分流数据
• 为每个测试名称配置OLE DB Destination组件，将数据写入对应的数据表

方式二：PowerShell批量导入后解析

先用PowerShell批量将XML文件导入数据库，再执行解析SQL：

$folderPath = "C:\YourXMLFolder"
$serverName = "YourServerName"
$databaseName = "EOL_Testers"

# 遍历文件夹导入XML到数据库
Get-ChildItem -Path $folderPath -Filter *.xml | ForEach-Object {
    $xmlContent = Get-Content $_.FullName -Raw
    # 转义XML中的单引号
    $escapedXml = $xmlContent -replace '''', '''''
    $sql = "INSERT INTO XMLFilesTable (XMLData) VALUES ('$escapedXml')"
    Invoke-SqlCmd -ServerInstance $serverName -Database $databaseName -Query $sql
}

导入完成后，执行前面的动态SQL即可完成解析。

内容的提问来源于stack exchange，提问作者Jan Kováč