Importing XML into SQL Tables using SSIS package

Question

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?

Answer 1

Hi @him tim
If I understand your question correctly, you probably need this update query:

;WITH CTE AS  
(  
 SELECT A.*,I.id_pk AS New_id_Pk  
 FROM Address_Table A JOIN Individual_Table I ON A.uniqueID=I.uniqueID  
)  
UPDATE CTE   
SET id_pk = New_id_Pk  

Of course, it would be better if you could provide the update query you tried before, we can provide some suggestions based on your query.

Best regards,
LiHong