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: 537

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: 247154
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;
```

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: 247154 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?

Jobs90312 Mr or Mrs. 500 Points: 537 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....