SQLTeam.com | Weblogs | Forums

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

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

Does one file containmore than one unique record?

@yosiasz

Yes. Update files contains multiple records.

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