如何使用 SSIS 包将 XML 文件加载到数据库中？

Question

我在 Visual Studio 2008 中使用 SSIS。我有许多 XML 文件需要处理并放入现有的数据库结构 (SQL Server 2005) 中。这是我第一次尝试使用 SSIS，但有点卡住了。我找到了 XML 数据流任务，为其分配了一个测试 xml 文件及其关联的 XSD，并将一个节点映射到数据库表。我的问题是，如何将多个 xsd 节点与多个表关联起来？我当然不必为每个表设置 XML 源吗？

Answer 1

下面是一个可能的选项，演示如何将具有相同定义的多个 XML 文件加载到 SQL Server 表中。该示例使用SQL Server 2008 R2 和SSIS 2008 R2。此处显示的示例在 XML Source 组件的帮助下，使用 SSIS Data Flow Task 将三个 XML 文件加载到 SQL 表中。

分步过程：

1. 使用SQL 脚本部分中给出的脚本创建名为dbo.Items 的表。
2. 使用 XSD 文件 部分下提供的内容在文件夹路径 C:\temp\xsd 中创建名为 Items.xsd 的 XSD 文件。
3. 在文件夹路径 C:\temp\xml 中创建三个 XML 文件，即 Items_1.xml、Items_2.xml 和 Items_3.xml 使用 XML 文件 部分下提供的内容。
4. 在包上，创建 3 个变量，即 FileExtension、FilePath 和 FolderPath，如屏幕截图 #1 所示。
5. 在包的连接管理器上，创建一个名为 SQLServer 的 OLE DB 连接以连接到 SQL Server 实例，如屏幕截图 #2 所示。
6. 在 Control Flow 选项卡上，在 Foreach 循环容器中放置一个 Foreach 循环容器 和一个 Data Flow Task，如屏幕截图所示 # 3。
7. 配置 Foreach 循环容器，如屏幕截图 #4 和 #5 所示。
8. 双击数据流任务以导航到数据流选项卡。放置一个 XML Source 组件和一个 OLE DB Destination，如屏幕截图 #6 所示。
9. 配置 XML Source，如屏幕截图 #7 和 #8 所示。 XML 文件路径将从变量 FilePath 中检索。该变量将由 Foreach 循环容器 填充。
10. 配置 OLE DB 目标，如屏幕截图 #9 和 #10 所示。
11. 屏幕截图#11 和#12 显示了包的执行情况。
12. 屏幕截图 #13 显示包执行之前的表数据。屏幕截图 #14 显示包执行之后的表数据。表 dbo.Items 中的数据现在包含三个 XML 文件中的数据。

希望有帮助。

SQL 脚本：

CREATE TABLE [dbo].[Items](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ItemNumber] [nvarchar](50) NOT NULL,
    [ItemName] [nvarchar](60) NOT NULL,
    [Price] [numeric](18, 2) NOT NULL,
CONSTRAINT [PK_Items] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY]
GO

XSD 文件

<xsd:schema xmlns:schema="ItemsXSDSchema" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" targetNamespace="ItemsXSDSchema" elementFormDefault="qualified">
    <xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />
    <xsd:element name="Items">
        <xsd:complexType>
            <xsd:sequence>
                <xsd:element minOccurs="0" maxOccurs="unbounded" name="Item">
                    <xsd:complexType>
                        <xsd:sequence>
                            <xsd:element name="Id" type="sqltypes:int" />
                            <xsd:element name="ItemNumber">
                                <xsd:simpleType>
                                    <xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">
                                        <xsd:maxLength value="20" />
                                    </xsd:restriction>
                                </xsd:simpleType>
                            </xsd:element>
                            <xsd:element name="ItemName">
                                <xsd:simpleType>
                                    <xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">
                                        <xsd:maxLength value="60" />
                                    </xsd:restriction>
                                </xsd:simpleType>
                            </xsd:element>
                            <xsd:element name="Price">
                                <xsd:simpleType>
                                    <xsd:restriction base="sqltypes:numeric">
                                        <xsd:totalDigits value="18" />
                                        <xsd:fractionDigits value="2" />
                                    </xsd:restriction>
                                </xsd:simpleType>
                            </xsd:element>
                        </xsd:sequence>
                    </xsd:complexType>
                </xsd:element>
            </xsd:sequence>
        </xsd:complexType>
    </xsd:element>
</xsd:schema>

XML 文件

Items_1.xml

<?xml version="1.0"?>
<Items xmlns="ItemsXSDSchema">  
    <Item>
        <Id>1</Id>
        <ItemNumber>I2345343</ItemNumber>
        <ItemName>Monitor</ItemName>
        <Price>299.99</Price>
    </Item>
</Items>

Items_2.xml

<?xml version="1.0"?>
<Items xmlns="ItemsXSDSchema">  
    <Item>
        <Id>1</Id>
        <ItemNumber>J1231231</ItemNumber>
        <ItemName>Mouse</ItemName>
        <Price>29.99</Price>
    </Item>
</Items>

Items_3.xml

<?xml version="1.0"?>
<Items xmlns="ItemsXSDSchema">  
    <Item>
        <Id>1</Id>
        <ItemNumber>K0456212</ItemNumber>
        <ItemName>Keyboard</ItemName>
        <Price>49.99</Price>
    </Item>
</Items>

屏幕截图 #1：

屏幕截图 #2 ：

屏幕截图#3：

屏幕截图 #4：

屏幕截图 #5：

屏幕截图 #6：

< img src="https://i.sstatic.net/cEqLB.png" alt="6">

屏幕截图 #7：

屏幕截图 #8：

屏幕截图#9:

屏幕截图 #10:

屏幕截图 #11:

屏幕截图 #12：

屏幕截图 #13： >

屏幕截图 #14：

Answer 2

您还需要将 @[user::FilePath] 添加到数据流任务中的 [XML Source].[XMLData]，否则包会显示无源包执行后发现文件。