How to import updated records from XML files into SQL Database?

Question

Hello everyone,

So from last few weeks I was trying to design a SSIS package that would read some XML files that I have and move the data from it to the multiple tables I want.

These file contains different nodes like Individual (parent node) and Address, Alias, Articles (all child nodes of Individual) etc.

Data in those files look like this:

<Individuals>  
   <Individual>  
       <UniqueID>1001</UniqueID>  
       <Name>Ben</Name>  
       <Soft_Delete>N</Soft_Delete>  
       <Soft_Delete_Date>NULL</Soft_Delete_Date>  
       </Individual>  
       <Addresses>  
            <Address>  
          <Address_Line_1>House no 280</Address_Line_1>  
          <Address_Line_2>NY</Address_Line_2>  
             <Country>US</Country>  
       <Soft_Delete>N</Soft_Delete>  
       <Soft_Delete_Date>NULL</Soft_Delete_Date>  
                </Address>  
            <Address>  
          <Address_Line_1>street 100</Address_Line_1>  
          <Address_Line_2>California</Address_Line_2>  
             <Country>US</Country>  
       <Soft_Delete>N</Soft_Delete>  
       <Soft_Delete_Date>NULL</Soft_Delete_Date>  
                </Address>  
               </Addresses>  
                   </Individuals>  

I was successful in designing it and now I have a different task.

The files I had were named like this: Individual_1.xml,Individual_2.xml,Individual_3.xml etc.

Now I have received some new files which are named like this:

Individual_UPDATE_20220716.xml,Individual_UPDATE_20220717.xml,Individual_UPDATE_20220718.xml,Individual_UPDATE_20220720.xml etc

Basically these files contains the updated information of previously inserted records

OR

There are totally new records

For example:

A record or a particular information like Address of an Individual was Soft Deleted.

Now I am wondering how would I design or modify my current SSIS package to update the data from these new files into my database?

Any guidance would be appreciated....

Thank you...

Answer 1

Hi @him tim ,

Here is an approach you can take.

1. Create staging tables as carbon copy of the destination tables in the original SSIS package that does INSERT operation.
2. Create SSIS package like the original to populate staging tables with updated data from the *_UPDATE_*.xml files.
3. Use T-SQL MERGE statement - by using SSIS Execute SQL Task - to update destination tables using staging table as a source.

Answer 2

Hi @him tim ,

You may first load data to staging tables and then use sql command to update or remove the unnecessary rows.

Or you may load the files to the table first, and then use SSIS Sort Transformation to removing duplicates rows.

Regards，

Zoe

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Hi @him tim ,
In this situation, you may modify the code like

merge into address as t  
 using xml s  
 on t.Address=s.Address and t.uniqueID=s.uniqueID  
 when matched then  
 update  
 set t.city=s.city  
 when not matched then  
 insert values(s.address,s.city,s.uniqueID);  

https://www.mssqltips.com/sqlservertip/1704/using-merge-in-sql-server-to-insert-update-and-delete-at-the-same-time/

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. :)