Importing XML into SQL Tables using SSIS package

Question

Post reply

Importing XML into SQL Tables using SSIS package

Jobs90312

Mr or Mrs. 500

Points: 530

June 24, 2022 at 6:39 am

Go to Answer

#4054194

Hi everyone,

I have a XML file which consists of data like this:

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

I have designed ssis package and

Now I am able to move Individual data to Individual table.

And Address data to Address table during the import process.

I have a primary key in my Individual table (id_pk) which is auto-generated:

Address table in SQL:

I am having trouble moving the main id_pk from individual into address table.

I have designed a update query but its taking too long to update in to address table.

What would be the best solution?

Phil Parkin

SSC Guru

Points: 246695
More actions
June 24, 2022 at 9:01 am
Answer
#4054262
Yikes, a WHILE loop. No wonder it's slow.
The following query should do the UPDATE in a single hit. But please test it thoroughly before running it anywhere important.
```
UPDATE a
SET Address_UniqueID_FK = i.id_pk
FROM address a
    JOIN individual i
        ON a.Address_Record_ID_FK = i.RECORD_ID
WHERE a.Address_UniqueID_FK IS NULL;
```
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

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: 246695 More actions · Answer 1

Updating the column by matching on UniqueId should not take such a long time. Can you show us your UPDATE query? Approximately how many rows of data need to be updated?

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Jobs90312 Mr or Mrs. 500 Points: 530 More actions · Answer 2

Above XML data and SQL tables are just sample.

The XML files are multiple and way too big. Records are in millions.

Here is my query:

DECLARE @VAR1 INT = 1;
DECLARE @VAR2 INT = (SELECT COUNT(ADD_ID) FROM ADDRESS 
);
SELECT @VAR2;


WHILE (@VAR2 > 0)
BEGIN -- BEGIN OF 1ST WHILE LOOP

IF(
(SELECT Address_UniqueID_FK FROM Address 
WHERE ADD_ID = @VAR1 ) IS NULL
)
BEGIN; --BEGIN OF FIRST IF

UPDATE Address
SET Address_UniqueID_FK = ( 
SELECT IND.UNIQUEID 
FROM Individual IND
INNER JOIN Address ADDR
ON IND.RECORD_ID = ADDR.Address_Record_ID_FK
WHERE ADDR.Add_Id = @VAR1
)
WHERE Add_Id = @VAR1


END; -- BEGIN OF FIRST IF

SET @VAR2=@VAR2-1;
SET @VAR1=@VAR1+1;

END; -- END OF FIRST WHILE LOOP

I basically first transfer all the data of individual and address nodes of XML in to their respective tables and columns, then I try to update the UniqueID from Individual table to Address table based on Record_ID in both tables. Which takes way too long. I let this query run for 1 hour and still it wasnt completed.

Rows are around 100,000 for 1 file.

ALSO NOTE THAT ABOVE QUERY CONTAINS THE ACTUAL NAMES OF THE COLUMNS, AND IN OP I SIMPLIFIED AND POSTED THE ISSUE WITH SIMPLE NAMES....

This reply was modified 1 year, 10 months ago by Jobs90312.
This reply was modified 1 year, 10 months ago by Jobs90312.

Matthew145 Grasshopper Points: 17 More actions · Answer 3

I will endeavor to figure it out for extra.

MyMileStone Card

This reply was modified 1 year, 10 months ago by Matthew145.

frederico_fonseca SSCoach Points: 16017 More actions · Answer 4

frederico_fonseca

SSCoach

Points: 16017

June 30, 2022 at 6:51 am

#4056894

spam again - we really should block this thing