在BizTalk中将XML数据导入SQL Server数据库

1. 准备SQL Server目标表 #

首先创建包含XML所有字段及额外字段的目标表，其中ID设为自增主键，CreatedDate设为默认当前时间：

CREATE TABLE BalanceData (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Company NVARCHAR(50) NOT NULL,
    Account NVARCHAR(50) NOT NULL,
    Type NVARCHAR(20) NOT NULL,
    Description NVARCHAR(100) NOT NULL,
    Units NVARCHAR(20),
    Branch NVARCHAR(20) NOT NULL,
    Department NVARCHAR(20) NOT NULL,
    Activity NVARCHAR(50) NOT NULL,
    Direction NVARCHAR(20) NOT NULL,
    Mode NVARCHAR(20) NOT NULL,
    Year NVARCHAR(4) NOT NULL,
    Period NVARCHAR(6) NOT NULL,
    AmountInPeriod DECIMAL(18,2) NOT NULL,
    Currency NVARCHAR(10) NOT NULL,
    CreatedDate DATETIME DEFAULT GETDATE()
)

2. 创建BizTalk源架构 #

• 在BizTalk项目中，基于提供的XML生成源架构：右键项目→Add→Add Generated Items→Generate Schema，选择目标XML文件，导入后调整字段数据类型。
• 注意处理命名空间：Root节点的xmlns="http://schema.Balance_SourceSchema"和Balance节点的空命名空间，确保架构能正确解析XML中的所有字段。
• 针对AmountInPeriod字段，源XML中是带引号和逗号的字符串，需在后续映射中做类型转换处理。

3. 配置SQL适配器与目标架构 #

• 使用BizTalk SQL适配器连接目标SQL Server，生成对应BalanceData表的目标架构：右键项目→Add→Add Generated Items→Consume Adapter Service，选择SQL适配器，连接到数据库后选择BalanceData表的Insert操作，自动生成目标架构。
• 目标架构中ID和CreatedDate字段可设置为可选，因为ID由SQL自增生成，CreatedDate用表默认值，无需BizTalk传入数据。

4. 开发数据映射（Map） #

在BizTalk项目中创建源架构到目标架构的映射，完成以下关键配置：

• 移除字段引号：源XML中所有字段值都带双引号（如<Company>"PKG"</Company>），使用String.Replace函数将字段值中的"替换为空字符串。
• 转换金额字段：对AmountInPeriod，先移除引号和逗号，再用Convert.ToDecimal函数转换为数值类型。
• 跳过自增与默认字段：ID字段无需映射；CreatedDate若用SQL默认值则直接跳过，若需BizTalk生成，使用System.DateTime.Now()函数赋值。
• 其余字段直接一对一映射即可。

5. 配置BizTalk端口与流程 #

• 接收端口：创建文件接收端口（假设XML以文件形式传入），指定接收文件夹，关联源架构，确保接收管道能正确解析XML消息。
• 发送端口：创建SQL发送端口，配置SQL Server连接字符串，选择之前生成的目标架构与Insert操作，将开发好的映射关联到发送端口。
• 若需更复杂的业务逻辑，可创建Orchestration，将接收的XML消息通过映射转换后发送到SQL端口。

6. 部署与测试 #

• 部署BizTalk项目到BizTalk Server，启动接收位置和发送端口。
• 将测试XML文件放入接收文件夹，检查BizTalk管理控制台中的消息状态，验证SQL Server的BalanceData表中是否正确插入数据，重点确认：
  • 所有字段值已移除引号，金额字段转换为正确数值。
  • ID自动生成，CreatedDate正确填充。

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