MS SQL中解析XML文件至数据表的技术求助

针对你在MS SQL中存储数千个XML、需要解析为含ID、EolPresetting日期及测试值的数据表用于Power BI的需求，提供三个可行方案：

方案一：SQL Server内置XML函数直接解析（推荐） #

如果XML结构固定，直接用SQL的XML原生函数批量解析，生成视图或持久化表，Power BI可直接对接。

前提假设 #

假设你存储XML的表为XmlStorage，字段：

• Id：XML记录的唯一标识（INT类型）
• XmlContent：存储XML内容的字段（XML类型）

XML结构示例（请根据实际结构调整XPath）：

<Root>
  <EolPresetting>
    <Date>2024-05-20</Date>
  </EolPresetting>
  <Tests>
    <Test>
      <Name>TestA</Name>
      <Value>95.5</Value>
    </Test>
    <Test>
      <Name>TestB</Name>
      <Value>88</Value>
    </Test>
  </Tests>
</Root>

操作步骤 #

1. 创建目标数据表（可选，若需持久化数据）

CREATE TABLE ParsedTestData (
    Id INT,
    EolPresettingDate DATE,
    TestName NVARCHAR(100),
    TestValue DECIMAL(18,2)
)

2. 批量插入解析后的数据

INSERT INTO ParsedTestData (Id, EolPresettingDate, TestName, TestValue)
SELECT
    x.Id,
    -- 提取EolPresetting日期，根据实际XML路径调整XPath
    x.XmlContent.value('(/Root/EolPresetting/Date)[1]', 'DATE') AS EolPresettingDate,
    -- 拆分每个Test节点，提取测试名称和值
    testNode.value('(Name)[1]', 'NVARCHAR(100)') AS TestName,
    testNode.value('(Value)[1]', 'DECIMAL(18,2)') AS TestValue
FROM XmlStorage x
-- 交叉应用拆分Test节点为行
CROSS APPLY x.XmlContent.nodes('/Root/Tests/Test') AS Tests(testNode)

3. 创建视图（若无需持久化，Power BI直接读视图）

CREATE VIEW vw_ParsedTestData
AS
SELECT
    x.Id,
    x.XmlContent.value('(/Root/EolPresetting/Date)[1]', 'DATE') AS EolPresettingDate,
    testNode.value('(Name)[1]', 'NVARCHAR(100)') AS TestName,
    testNode.value('(Value)[1]', 'DECIMAL(18,2)') AS TestValue
FROM XmlStorage x
CROSS APPLY x.XmlContent.nodes('/Root/Tests/Test') AS Tests(testNode)

注意事项 #

• 务必根据实际XML结构修改XPath表达式，比如节点名称、层级变化
• 若日期格式非标准，需用CONVERT函数转换为DATE类型
• 数千条XML的量级下，该方案性能足够，无需额外优化

方案二：SSIS实现ETL批量处理 #

如果需要复杂的数据清洗、错误捕获或定期同步，用SSIS构建ETL流程：

操作步骤 #

1. 新建SSIS包，添加OLE DB源，连接到存储XML的SQL表，读取Id和XmlContent字段
2. 在数据流任务中添加脚本组件（作为转换）：
   • 输入列选择Id和XmlContent
   • 添加输出列：Id(INT)、EolPresettingDate(DATE)、TestName(NVARCHAR(100))、TestValue(DECIMAL(18,2))
   • 编写C#/VB脚本解析XML，遍历Test节点并输出行

脚本示例（C#）：

using System.Xml;
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    XmlDocument doc = new XmlDocument();
    doc.LoadXml(Row.XmlContent);
    
    // 获取EolPresetting日期
    XmlNode dateNode = doc.SelectSingleNode("/Root/EolPresetting/Date");
    DateTime eolDate = DateTime.Parse(dateNode.InnerText);
    
    // 遍历所有Test节点，输出每行数据
    XmlNodeList testNodes = doc.SelectNodes("/Root/Tests/Test");
    foreach (XmlNode testNode in testNodes)
    {
        Output0Buffer.AddRow();
        Output0Buffer.Id = Row.Id;
        Output0Buffer.EolPresettingDate = eolDate;
        Output0Buffer.TestName = testNode.SelectSingleNode("Name").InnerText;
        Output0Buffer.TestValue = decimal.Parse(testNode.SelectSingleNode("Value").InnerText);
    }
}

3. 添加OLE DB目标，连接到目标数据表，将解析后的数据写入
4. 配置SQL Server Agent作业，定期执行SSIS包以同步新增XML数据

方案三：Power BI直接解析XML #

若不想在SQL端处理，可让Power BI直接读取并解析XML字段：

操作步骤 #

1. 在Power BI Desktop中连接到SQL Server数据库，选择存储XML的表
2. 进入Power Query编辑器，选中XmlContent列：
   • 点击转换选项卡 → 解析 → XML
   • 展开EolPresetting节点，提取Date列并重命名为EolPresettingDate
   • 展开Tests节点下的Test列表，选择到行，将每个Test拆分为单独行
   • 展开Test行的Name和Value列，重命名为TestName和TestValue
3. 调整数据类型：将EolPresettingDate设为日期类型，TestValue设为数值类型
4. 加载数据到Power BI模型，即可用于制作报表

注意事项 #

• 数千条XML的量级下性能无压力，但数据量持续增长时，建议优先用SQL端预处理
• XML结构变化时，需同步调整Power Query中的解析步骤

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