Loading two header / detail XML files out of sequence

Question

Loading two header / detail XML files out of sequence

FairFunk

SSC Eights!

Points: 930
More actions
October 26, 2010 at 4:49 am

#228425

SQL Server 2008.
Morning,
If someone could offer their advice it would be very much appreciated.
Currently I’m looking to import two XML files, Donation amount and Donation Maker.
The Donation Amount file would comprise of an Amount field and a DonationMakerID, and the Donation Maker file would comprise of a Name field and DonationMakerID. The Donation Maker would make the donation of a certain amount brought back in the Amount file.
My issue is I will occasionally receive a Donation Amount file with a Donation Maker ID that doesn’t relate to any Donation makers currently in the database but will probably relate to a Donation MakerID in the Donation Maker XML file which I will receive later that day.
Now what would be the best way to store the Donation Amount information until I receive the Donation Maker file? I could obviously not load the Donation amount file until I have a matching Donation MakerID but I would rather load the file as and when I get them.
Ideally I need a FK constraint that only enforces referential integrity in the Donation Amount table for IDs that already exist in the Donation Maker table…
I have a few ideas for example, two columns in dbo.DonationAmount of DonationMakerID and XMLDonationMakerID where DonationMakerID can allow NULLs. Then when I receive a Donation Amount file I load the DonationMakerID into the XMLDonationMakerID field and if there is a matching ID in the maker table I load that to the Donation makerID column otherwise I leave it NULL. Then when I receive subsequent Donation Maker XML files I can check the XMLDonationMakerID field against the ID in the Donationmaker XML file and if I find one that matches update DonationMakerID.
Hopefully that all makes sense.
Many thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

I would load the Donation Amount file (maybe the Donation Maker, too) into staging tables first and move into the final table from there. This would allow for any logic check, missing information or the like without the need to call the xml files over and over again.

Also, I wouldn't have to worry when to expect the missing information.

If needed, I'd add a few lines of code to send me a note if there are rows in the staging table(s) for longer than "X" days.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

FairFunk SSC Eights! Points: 930 More actions · Answer 2

Hi Lutz

Cheers for the advice.

In the end I've decided that when I import the XML DonationDetail file I import all IDs from the file to XMLDonationDetailID regardless and the amount to the amount field.I also LEFT JOIN the Donation Maker table, if the ID matches I update the DonationDetailID to be the relevent ID. So if I dont currently have the donation maker in the system, DonationID is null and XMLDonationID is populated with the missingID. Then when I receive the next Donation Maker XML file I check to see if XMLDonationID exists and if so update the DonationID in the DonationAmount table.

Although unlike your solution I am now stuck with an extra column which I wouldn't have needed if I were to go with your idea.

Thanks again

FairFunk SSC Eights! Points: 930 More actions · Answer 3

HI Lutz,

Ok change of plan, i've redesigned my import procedure to inculde a import staging table for any problem Donation Amount files etc based on your advice, and to go from there.

Many thanks