如何使用 SSIS 将数据从数据库表导出到 XML 文件

引言

我有一个任务，需要将 SQL Server 数据库表中的数据导出到 XML 文件。下面是使用 SSIS 实现这一目标的一种简单方法。无需脚本组件！

背景

毫无疑问，SSIS 是完成此类任务最自然的选择。SSIS 的核心功能之一就是将数据从数据库传输到文件。通常在 SSIS 中，这通过一个“数据流任务”构建块来完成。这个块包含两个子块：一个用于数据库（源），另一个用于文件（目标）。第一个子块从工具箱的“数据流源”中选择。您可能会在工具箱的“数据流目标”部分找到第二个子块。但是……该部分中没有“XML 文件”！因此，不幸的是，SSIS 没有“内置”将数据库表中的数据导出到 XML 文件的方法。仍然可以使用“平面文件目标”作为目标子块来创建一个常规文本文件。然后需要进行一些额外的操作，将文件中的文本格式化为 XML。毕竟，XML 不过是格式化的文本。 

我在网上找到的大多数文章都建议使用“脚本任务”块并编写代码，利用 .NET 库来生成 XML 输出。这是一种灵活而强大的方法，但恕我直言，这根本没有必要，就像不需要用枪打蚊子一样。您可以使用“内置”的原始方法执行如此简单的任务。 

这里介绍的 SSIS 使用标准的“数据流任务”构建块，没有任何 .NET 代码。它使用标准的 T-SQL（SQL Server 的 SQL）。使用带有 FOR XML 子句的嵌套 SELECT 命令将表的层次结构表示为 XML。它还用于添加自定义的 XML 标签（开始和结束标签）。

数据示例

源数据组织在两个数据库表的层次结构中：**Employees** 服务于 **Customers**，它们之间的关系是“多对多”，存储在第三个表 **EmployeesCustomers** 中。此表描述了哪个员工服务于哪个客户。通常，一个员工服务多个客户。有些客户可以被多个员工服务。SSIS 将此数据导出到一个 XML 文件，该文件如下所示：

<Company>
  <Employees>
    <Employee Id="..." Name="...">
      <Customers>
        <Customer Id="..." Name="...">
        ...
      </Customers>
    </Employee>
    ...
  </Employees>
</Company>

请注意，被多个员工服务的客户会在 XML 文件中出现多次，每次都出现在不同的员工名下。 

现在让我们构建示例数据。在 SQL Server Management Studio 中运行以下 SQL 命令来构建示例表。（我使用了圣经中的人物作为员工，当代的名人作为客户，以增加趣味性。这只是一个说明工作原理的示例。当然，您可以使用自己的数据和自己的层次结构）

CREATE TABLE [dbo].[Employees](
        [EmployeeID] [bigint] NOT NULL,
        [FirstName] [varchar](20) NULL,
        [Title] [varchar](50) NULL,
        [BirthDate] [datetime] NULL,
        [Salary] [money] NULL,
CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED
(
        [EmployeeID] ASC
))
 
CREATE TABLE [dbo].[Customers](
        [CustomerID] [int] NOT NULL,
        [CustomerName] [varchar](50) NULL,
CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED
(
        [CustomerID] ASC
))
 
CREATE TABLE [dbo].[EmployeesCustomers](
        [EmployeeID] [int] NOT NULL,
        [CustomerID] [int] NOT NULL,
CONSTRAINT [PK_EmployeesCustomers] PRIMARY KEY CLUSTERED
(
        [EmployeeID] ASC,
        [CustomerID] ASC
))
 
INSERT INTO Employees VALUES (1,'Abraham','Team Leader'   ,'1947-11-25', 100.00)
INSERT INTO Employees VALUES (2,'Sarah'  ,'HR Manager'    ,'1967-04-20', 100.00)
INSERT INTO Employees VALUES (3,'Hagar'  ,'Office Cleaner','1978-08-03', 10.00)
INSERT INTO Employees VALUES (4,'Ishmael','Mail Deliverer','1989-12-01', 20.00)
INSERT INTO Employees VALUES (5,'Isaac'  ,'Developer'     ,'1995-01-01', 50.00)
INSERT INTO Employees VALUES (6,'Rebekah','QA Person'     ,'1995-05-13', 50.00)
 
INSERT INTO Customers VALUES (1,'Serena Williams')
INSERT INTO Customers VALUES (2,'Pat Robertson')
INSERT INTO Customers VALUES (3,'Paris Hilton')
INSERT INTO Customers VALUES (4,'Barak Obama')
INSERT INTO Customers VALUES (5,'Arnold Schwarzenegger')
INSERT INTO Customers VALUES (6,'Oprah Winfrey')
INSERT INTO Customers VALUES (7,'Prince El Saudi')
  
INSERT INTO EmployeesCustomers VALUES (1,5)
INSERT INTO EmployeesCustomers VALUES (1,6)
INSERT INTO EmployeesCustomers VALUES (1,7)
INSERT INTO EmployeesCustomers VALUES (2,1)
INSERT INTO EmployeesCustomers VALUES (2,2)
INSERT INTO EmployeesCustomers VALUES (3,3)
INSERT INTO EmployeesCustomers VALUES (3,4)

使用 SSIS

打开一个新的 SSIS 项目。在屏幕底部通常的“连接管理器”窗口中的空白区域右键单击。我们将添加两个连接管理器：一个用于生成的 XML 文件，另一个用于 SQL Server 命令作为数据源。

从菜单中选择“新建平面文件连接…”。在“常规”部分，将“连接管理器名称”字段填写为 FileOut，将“文件名”填写为生成的 XML 文件名。勾选“Unicode”以避免与代码页相关的复杂性。“区域设置”字段会根据您的本地语言和国家/地区自动选择。我的设置为希伯来语（以色列）。其余字段保留默认值。

在左侧的“说明”下点击“高级”。点击“新建”按钮中的向下箭头，然后从菜单中选择“添加列”。

现在添加了一个新列。将“名称”字段填写为 COL_XML，将“数据类型”字段填写为 Unicode 文本流 [DT_NTEXT]。点击“确定”。

现在是数据源。右键单击“连接管理器”，然后从菜单中选择“新建 OLE DB 连接…”。给它命名为 SQLIn。在“连接管理器”窗口中，填写“服务器名称”和“选择或输入数据库名称”字段。点击“测试连接”按钮，以确保数据库连接一切正常。点击“确定”。

到目前为止，您应该有两个连接管理器，看起来像这样：

现在我们要添加将完成工作的唯一构建块。从工具箱中选择“数据流任务”。

双击它。

从工具箱的“数据流源”部分选择“OLE DB 源”。右键单击它并选择“编辑…”。在“OLE DB 源编辑器”中，为“OLE DB 连接管理器”选择 SQLIn，为“数据访问模式”选择“SQL 命令”。现在是任务最重要的部分：带有 FOR XML 子句的嵌套 SQL SELECT 命令： 

SELECT (
  SELECT TOP 1 NULL as N,
    (SELECT Employee.EmployeeId as Id, Employee.FirstName as Name,
      (SELECT TOP 1 NULL as N,
        (SELECT Customer.CustomerID as Id, CustomerName as Name
         FROM EmployeesCustomers INNER JOIN Customers Customer
         ON EmployeesCustomers.CustomerID = Customer.CustomerID
         WHERE EmployeesCustomers.EmployeeId = Employee.EmployeeId
         ORDER BY Customer.CustomerID
         FOR XML AUTO, TYPE)
       FROM Customers
       FOR XML AUTO, TYPE)
     FROM EmployeesCustomers INNER JOIN Employees Employee
     ON EmployeesCustomers.EmployeeID = Employee.EmployeeID
     GROUP BY Employee.EmployeeId, Employee.FirstName
     ORDER BY Employee.EmployeeId
     FOR XML AUTO, TYPE)
  FROM Employees
  FOR XML AUTO, ROOT('Company')
) AS COL_XML   

基本上，此 SQL 语句是三个表（**Customers**、**EmployeesCustomers** 和 **Employees**）之间标准连接的一种变体，用于获取负责特定客户的员工，以及由多个员工负责的客户，这被称为“多对多”关系。注意连接三个表的两个 INNER JOIN 子句。如果您的任务需要更简单的关系，例如“一对多”，那么您只需要一个 INNER JOIN 子句，整个 SQL 将会更短。 在获取关系后，会对员工进行分组以消除同一员工的多个实例。为此，我使用了 ORDER BY 子句。 

将此文本复制并粘贴到“SQL 命令文本”中： 

点击“预览”按钮。您将在单个列 COL_XML 中看到 XML 文本： 

请注意，我们的 SELECT 语句被包含在 SELECT (...) AS COL_XML 中，以使输出为文本。否则，它看起来会像这样：

点击“关闭”和“确定”。

从工具箱的“数据流目标”部分选择“平面文件目标”。右键单击它并选择“编辑…”。在“平面文件目标编辑器”中，为“平面文件连接管理器”选择 FileOut。

点击左侧的“列”，可以看到它识别出了名为 COL_XML 的唯一列。

点击“确定”。用绿色箭头连接“OLE DB 源”和“平面文件目标”块。

如果一切正常，则“平面文件目标”块上的红色错误标记将消失。

现在运行您的项目。最终结果将是您之前在 FileOut 连接管理器中指定的 XML 文件。找到它并在浏览器中打开。它看起来会像这样：

我们完成了。这看起来有很多步骤，但实际上完成它只需要很短的时间。

关注点

XML 由 FOR XML 子句形成。AUTO 修饰符使用表的名称和列的名称作为 XML 标签。

要使用 ROOT('root_tag_name') 修饰符将整个 XML 文档包含在根标签中，如下所示： 

FOR XML AUTO, ROOT('Company') 

要将多个子 XML 节点包含在一个父节点中，而没有 XML 属性，例如将多个 <Employee> 节点包含在一个 <Employees> 节点中，我使用了这个语句： 

SELECT TOP 1 NULL as N, (SELECT...) FROM TableName 

当然，这不是强制性的，如果您的生成的 XML 不需要它，可以省略。

要自定义 XML 属性的名称，我使用了 as 子句，例如：

Employee.EmployeeId as Id, Employee.FirstName as Name

历史

• 2013/08/15 - 首次提交版本。 
• 2013/08/18 - 修订以解释此方法为何有用并与其他方法进行比较。SELECT 语句已详细解释。