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