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

Question

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

Jobs90312

Mr or Mrs. 500

Points: 533
More actions
July 26, 2022 at 6:48 am

#4068228
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...
- This topic was modified 2 years, 3 months ago by Jobs90312.
March 19, 2024 at 8:19 am

This was removed by the editor as SPAM
March 19, 2024 at 9:56 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Seems that a good architecture for you would be to push the imported data into (truncated) staging tables and then execute one or more procs which MERGE the staged data into the final tables.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Phil Parkin SSC Guru Points: 246960 More actions · Answer 2

Suliman wrote:

Try SmartXML, it's very easy to upload data into DB with it.

This looks suspiciously like spam. To prove yourself, please explain the interaction between this product and SQL Server and how you would go about solving this problem.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

Please stop spamming. This is not the forum for that.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin