CODEWITHSUNDEEP
sql-serverxmlssisetl
dex
dex

Reputation: 21

Import XML files with different structures using SSIS

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.

Upvotes: 2

Views: 1004

Answers (3)

dex
dex

Reputation: 21

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!

Upvotes: 0

Hadi
Hadi

Reputation: 37368

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:

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.

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

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

Upvotes: 0

Piotr Palka
Piotr Palka

Reputation: 3169

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.

Upvotes: 1

Related Questions