SQLTeam.com | Weblogs | Forums

Importing XML into SQL Tables using SSIS package

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:

image

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?

I think it's going into 2 different directions. You can use a Fuzzy lookup:

Fuzzy Lookup Transformations in SSIS (sqlshack.com)

... or you can take a look at the query and look at the execution plan. Make sure that there is an index on the address table on uniqueid and that both formats are the same (integer?). But the loading of the table will take longer.

You can post your excecution plan with help of Brent Ozar.

Instructions - Brent Ozar Unlimited®

1 Like

Where is the remaining xml portion? I seem to remember you asking another question about generating xml from sql. But now you want to import it back in?

Can you please explain the whole story. The answers you will be provide when your questions are asked in pieces will vastly differ when asked as a requirement story.

If you are generating from sql to xml then turning around and re-importing it to SQL seems a bit unusual and might not be the best approach. Can you convince us otherwise? Unless this is some school stuff?

1 Like

Yes this is basically some school stuff.
I have been given different tasks to learn how to import xml into sql and how to export full sql data into XML. This question is different from my previous question and not linked to it directly.
And yes basically XML files I have are very large. I just posted here a sample from it that data looks like this in it.

So can anyone help me with this?