The FuelPathwayCode and PhysicalPathwayCode go into Parent table and transaction-item elements goes into the Child table. Parent table has a primary key and the child table has the related foreign key. The Parent table has FuelNameID and PhysicalPathwayID columns that are related to two lookup tables: FuelName and PhysicalPathway. The FuelNameID and PhysicalPathwayID values must be found in FuelName and PhysicalPathway tables from FuelPathwayCode and PhysicalPathwayCode elements.
What is the best approach to parse this XML document and insert into two related tables along with lookup table relationships?
Sample XML
<Fuel>
<FuelPathwayCode>CARBOB001</FuelPathwayCode>
<PhysicalPathwayCode>PP001</PhysicalPathwayCode>
<transaction-item type="Production or Import">
<StartDate>2010-04-01</StartDate>
<EndDate>2010-04-15</EndDate>
<Amount>1000</Amount>
<BusinessPartnerID>2</BusinessPartnerID>
<Description>Test description</Description>
</transaction-item>
<transaction-item type="Transfer: Release Obligation">
<StartDate>2010-04-01</StartDate>
<EndDate>2010-04-15</EndDate>
<Amount>1000</Amount>
<BusinessPartnerID>2</BusinessPartnerID>
<Description>Test description</Description>
</transaction-item>
</Fuel>