Insert XML Data Into SQL Table with Foreign Keys

Question

I have this XML File (there is data, but I deleted it for a purpose):

<NIACList>
<NIAC>
        <Number></Number>
        <SubmissionDate></SubmissionDate>
        <ExpirationDate  />
        <IssuerIDNO></IssuerIDNO>
        <IssuerName></IssuerName>
        <SuspensionPeriod/>
        <Cessation>
          <Basis  />
          <Date  />
        </Cessation>
        <Merchant>
          <IDNx></IDNx>
          <Name></Name>
          <Address>
            <Region></Region>
            <Locality></Locality>
            <Street></Street>
            <House></House>
            <Block  />
            <Flat  />
            <Phone  />
            <Fax  />
            <Email  />
          </Address>
        </Merchant>
        <CommercialUnit>
          <IDNx  />
          <Name  />
          <Type></Type>
          <Area></Area>
          <Location></Location>
          <Address>
            <Region></Region>
            <Locality></Locality>
            <Street></Street>
            <House></House>
            <Block  />
            <Flat  />
          </Address>
          <Activities>
            <Activity>
              <Code></Code>
              <Name></Name>
            </Activity>
          </Activities>
          <Goods>
            <Good>
              <Name></Name>
            </Good>
          </Goods>
          <WorkProgram  />
          <PublicSupplyUnit>
            <Capacity  />
            <TerraceCapacity  />
          </PublicSupplyUnit>
          <TradingAlcohol  />
          <TradingBeer  />
          <TradingTobaccoProducts  />
          <AmbulatoryTrading  />
          <MobileUnitTrading></MobileUnitTrading>
          <MobileUnit>
            <Type  />
            <Length  />
            <Width  />
            <Height  />
          </MobileUnit>
          <CommercialApparatusTrading></CommercialApparatusTrading>
          <CommercialApparatus>
            <Count  />
            <Length  />
            <Width  />
            <Height  />
          </CommercialApparatus>
        </CommercialUnit>
        <Modifications  />
      </NIAC>
</NIACList>

My task is to insert all XML data from this file into SQL Tables (SQL Server). I've inserted in most of the tables data, but here it is a problem. I have tables Address and Merchant. The first table doesn't have a foreign key, but the second table contains one, named IdAddress, so they could be relational. Is it possible to insert data into Merchant, so for each record from table Address will be linked with the IdAddress from Merchant.

Any help will be appreciated.

Answer 1

You need to insert the XML data in a buffer table - for example #temporary table. Then, first get the unique values that can be referred by the foreign key. Insert the one that do not exists. After that, join the #temporary table with the referenced table by the value and insert the value ID rather then the value.