SSIS实现多级XML解析并导入SQL Server分表方案咨询

一、前期准备：创建SQL Server目标表 #

根据XML中每个<test>节点的结构，分别创建对应的数据表，每张表必须包含OrderID字段，其余字段对应<value>的name属性。以Airbag_SAB表为例：

CREATE TABLE Airbag_SAB (
    OrderID VARCHAR(200) NOT NULL,
    MeasuredValue_Generator DECIMAL(10,6),
    LowLimit_Generator DECIMAL(10,2),
    HighLimit_Generator DECIMAL(10,2),
    MeasuredValue_BreakContact1 DECIMAL(10,6),
    MinumumValue_BreakContact1 DECIMAL(10,2),
    MeasuredValue_BreakContact2 DECIMAL(10,6),
    MinumumValue_BreakContact2 DECIMAL(10,2),
    TestOK BIT
)

其他表（如Airbag_CAB、Heating等）参照对应<test>节点的<value>字段结构创建，注意匹配数据类型。

二、SSIS包搭建步骤 #

1. 初始化项目与控制流 #

• 新建Integration Services项目，在控制流面板拖入数据流任务。

2. 配置XML源组件 #

• 进入数据流面板，添加XML源组件，双击配置：
  • 选择“文件连接管理器”，新建连接指向单个XML文件（批量处理时后续结合循环容器）。
  • 点击“生成XSD”，基于XML结构生成Schema文件并保存。
  • 数据访问模式选择“使用XML源文件中的数据”，指定XSD路径。
  • 展开节点树，选择eolresult\results\test节点作为输出，同时在高级编辑器中修改XPath，为每个<test>节点关联上层的Order ID：添加输出列OrderID，XPath设为../eolpresetting/order/@id。

3. 按Test名称拆分数据流 #

• 添加条件拆分组件，为每个Test名称设置拆分条件：
  • 条件1：[name] == "Airbag SAB"，输出名称AirbagSAB
  • 条件2：[name] == "Airbag CAB"，输出名称AirbagCAB
  • 依次为所有Test类型添加对应条件

4. 数据类型转换 #

XML中数值以逗号为小数分隔符，需转换为SQL Server支持的格式，同时布尔值转BIT类型：

• 为每个拆分后的数据流添加派生列组件：
  • 数值转换示例：(DT_DECIMAL,6)REPLACE([Measured value - Generator], ",", ".")
  • 布尔值转换示例：(DT_BOOL)[Test OK]

5. 导入到目标表 #

• 为每个拆分输出添加OLE DB目标组件：
  • 配置OLE DB连接管理器指向目标数据库。
  • 选择对应的数据表，完成列映射（确保OrderID及其他字段一一对应）。

6. 批量处理XML文件 #

若要处理文件夹内所有XML文件，在控制流中添加Foreach循环容器：

• 配置为“Foreach文件枚举器”，指定目标文件夹与.xml扩展名。
• 将数据流任务放入容器内，修改XML源的文件连接管理器，使用循环变量作为动态文件路径。

三、常见问题排查 #

• Order ID无法获取：检查XPath是否正确，在XML源预览中确认是否包含OrderID列。
• 数据转换失败：确保将逗号替换为点后再转换数值类型，布尔值True/False大小写需匹配。
• 字段映射错误：核对目标表字段与XML中<value>的name属性，避免名称或类型不匹配。

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