How to load an XML file into a database using an SSIS package?

Question

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?

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

Step-by-step process:

1. Create a table named dbo.Items using the script given under SQL Scripts section.
2. Create an XSD file named Items.xsd in the folder path C:\temp\xsd using the content provided under XSD File section.
3. 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.
4. On the package, create 3 variables namely FileExtension, FilePath and FolderPath as shown in screenshot #1.
5. 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.
6. 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.
7. Configure the Foreach Loop container as shown in screenshots #4 and #5.
8. 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.
9. 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.
10. Configure the OLE DB Destination as shown in screenshots #9 and #10.
11. Screenshots #11 and #12 show the package execution.
12. 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.

Hope that helps.

SQL Scripts:

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: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

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>

Screenshot #1:

Screenshot #2:

Screenshot #3:

Screenshot #4:

Screenshot #5:

Screenshot #6:

Screenshot #7:

Screenshot #8:

Screenshot #9:

Screenshot #10:

Screenshot #11:

Screenshot #12:

Screenshot #13:

Screenshot #14:

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