Import XML files with different structures using SSIS

Question

I have multiple XML files with different structures, yet they all share a specific set of nodes.

My objective is to only import those mutual nodes into an SQL Server table. How can I proceed, knowing that I can't generate an .Xsd for every type as there are many possible XML files variations? Thanks in advance.

Answer 1

Simple solution would be to load all these XML files into an XML table (2 columns: FileId and XmlData). This can be done as a 1st step of the package.
Then you will write a stored procedure and it will shred XML from this table into your final tables. This stored procedure will be called as a 2nd step in the SSIS package.

Answer 2

I don't think it is easy to do that using SSIS.

If you don't have other choices, you may use a Script Component as Source and parse the XML files using a C# or VB.NET script, then only generate output with desired columns.

You can refer to the following articles for more information about using Script component:

• Creating a Source with the Script Component
• SSIS Script Component Overview

SchemaMapper class library

From a while, I was working on a project called SchemaMapper. It is a class library developed using C#. You can use it to import tabular data from XML, and other formats having different structures into a unified SQL server table.

• SchemaMapper - C# schema mapping class library

You can refer to the project wiki for a step by step guide:

• SchemaMapper Wiki

---

Also, feel free to check the following answer, it may give you some insights:

• How to Map Input and Output Columns dynamically in SSIS?

Answer 3

So what I did as a solution to this particular case was: 1- add a C# script that reads the xml file and keep the common nodes only and save their values to my dts variables. 2- insert into my SQL Server table the variables I just populated. Both tasks were in a for each loop to go through all the xml files in a specific directory. Hope it helps!