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

I am using SSIS in Visual Studio 2008. I have many XML files that I need to process and place into an existing DB structure (SQL Server 2005). This is my first attempt at using SSIS and am a little stuck. I have found the XML Data Flow task, assigned it a test xml file and it's associated XSD, and mapped one node to a Database Table. My question is, how do I associate many xsd nodes with many tables? Surely I don't have to set up an XML source for each table?

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

2 个解决方案

#1

Here is a possible option which demonstrates how to load multiple XML files having same definition into an SQL Server table. The example uses SQL Server 2008 R2 and SSIS 2008 R2. The example shown here loads three XML files into an SQL table using SSIS Data Flow Task with the help of XML Source component.

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

Step-by-step process:

分步过程：

1. Create a table named dbo.Items using the script given under SQL Scripts section.
2. 使用SQL Scripts部分下给出的脚本创建名为dbo.Items的表。
3. Create an XSD file named Items.xsd in the folder path C:\temp\xsd using the content provided under XSD File section.
4. 使用XSD文件部分下提供的内容在文件夹路径C：\ temp \ xsd中创建名为Items.xsd的XSD文件。
5. Create three XML files namely Items_1.xml, Items_2.xml and Items_3.xml in the folder path C:\temp\xml using the content provided under XML Files section.
6. 使用XML Files部分下提供的内容在文件夹路径C：\ temp \ xml中创建三个XML文件，即Items_1.xml，Items_2.xml和Items_3.xml。
7. On the package, create 3 variables namely FileExtension, FilePath and FolderPath as shown in screenshot #1.
8. 在包上，创建3个变量，即FileExtension，FilePath和FolderPath，如屏幕截图＃1所示。
9. On the package's Connection Managers, create an OLE DB Connection named SQLServer to connect to the SQL Server Instance as shown in screenshot #2.
10. 在包的连接管理器上，创建名为SQLServer的OLE DB连接以连接到SQL Server实例，如屏幕截图＃2所示。
11. On the Control Flow tab, place a Foreach loop container and a Data Flow Task within the Foreach loop container as shown in screenshot #3.
12. 在Control Flow选项卡上，将Foreach循环容器和数据流任务放在Foreach循环容器中，如屏幕截图＃3所示。
13. Configure the Foreach Loop container as shown in screenshots #4 and #5.
14. 配置Foreach循环容器，如屏幕截图＃4和＃5所示。
15. Double-click on the Data Flow Task to navigate to the Data Flow tab. Place an XML Source component and an OLE DB Destination as shown in screenshot #6.
16. 双击“数据流任务”以导航到“数据流”选项卡。放置XML Source组件和OLE DB Destination，如屏幕截图＃6所示。
17. Configure the XML Source as shown in screenshot #7 and #8. The XML file path will be retrieved from the variable FilePath. This variable will be populated by the Foreach Loop container.
18. 配置XML Source，如屏幕截图＃7和＃8所示。将从变量FilePath中检索XML文件路径。该变量将由Foreach循环容器填充。
19. Configure the OLE DB Destination as shown in screenshots #9 and #10.
20. 配置OLE DB目标，如屏幕截图＃9和＃10所示。
21. Screenshots #11 and #12 show the package execution.
22. 屏幕截图＃11和＃12显示了包执行。
23. Screenshot #13 shows the table data before the package execution. Screenshot #14 shows the table data after the package execution. The data in the table dbo.Items now contains the data present in three XML files.
24. 屏幕截图＃13显示了包执行之前的表数据。屏幕截图＃14显示了包执行后的表数据。表dbo.Items中的数据现在包含三个XML文件中的数据。

Hope that helps.

希望有所帮助。

SQL Scripts:

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 File

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 Files

XML文件

Items_1.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

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

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>

Screenshot #1:

截图＃1：

Screenshot #2:

截图＃2：

Screenshot #3:

截图＃3：

Screenshot #4:

截图＃4：

Screenshot #5:

截图＃5：

Screenshot #6:

截图＃6：

Screenshot #7:

截图＃7：

Screenshot #8:

截图＃8：

Screenshot #9:

截图＃9：

Screenshot #10:

截图＃10：

Screenshot #11:

截图＃11：

Screenshot #12:

截图＃12：

Screenshot #13:

截图＃13：

Screenshot #14:

屏幕截图＃14：

#2

You also need to add @[user::FilePath] to the [XML Source].[XMLData] in the data flow task or the package says no source file found after package execution.

您还需要将@ [user :: FilePath]添加到数据流任务中的[XML Source]。[XMLData]，或者包中说包裹执行后没有找到源文件。