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

SSIS and Import/Export
,
1

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

2

Does one file containmore than one unique record?

3

@yosiasz

Yes. Update files contains multiple records.

4

there are many approaches.

There is a MERGE command appraoch:

;with src
as
(
select 
    t.x.value('UniqueID[1]','int') as UniqueID,
    t.x.value('Name[1]','varchar(150)') as Name
from @xml.nodes('/Individuals/Individual') t(x)

)

MERGE facundo AS Target
    USING src	AS Source
    ON Source.UniqueID = Target.UniqueID
    
    -- For Inserts
    WHEN NOT MATCHED BY Target THEN
        INSERT (UniqueID,Name) 
        VALUES (Source.UniqueID,Source.Name)
    
    -- For Updates
    WHEN MATCHED THEN UPDATE SET
        Target.Name	= Source.Name

also there is this approach

;with src
as
(
select 
    t.x.value('UniqueID[1]','int') as UniqueID,
    t.x.value('Name[1]','varchar(150)') as Name
from @xml.nodes('/Individuals/Individual') t(x)

)

update tgt
    set tgt.Asistentes = src.Name
  from facundo  tgt
  join src on src.UniqueID = tgt.Asistentes