Compare and Update or Drop and Reload?

[rsyCoder]

I have a file from a vendor in XML:

<Item>
    <company>Stuff</company>
    <UPC>876543210123</UPC>
    <prod_cd>M-PRODUCT         </prod_cd>
    <descrip>This thing has some describable charicteristics               </descrip>
    <brand xml:space="preserve">                    </brand>
    <color>CLR </color>
    <size>8   </size>
    <price>24.0000</price>
    <MSRP>72.0000</MSRP>
    <stockLevel>Y</stockLevel>
    <image_name>http://DOMAIN/FOLDER/FILE.jpg</image_name>
    <image_name_lg>http://DOMAIN/FOLDER/FILE-large.jpg</image_name_lg>
    <lastupdate>2011-07-07T19:35:28.257-07:00</lastupdate>
</Item>
 

I would like that file to land in a folder each day and be used to update a table in a SQL 2000 table. The program will use the file, I guess in terms of a datatable as an interface between the data and the DB, to add new entries, update changed entries (as any of that data can change between days), and if the entry is no longer present change the <stockLevel /> to an "N".

Am I stuck looping through the database table comparing every record or is there a process that already exisits for achieve this in a quicker and easier(on the DB) mannor? Then again, would it be better to drop the table each time and create a new one from the file?

Plus I would need to trim all the extra white space that is included in the data. Is there a way to do that across an entire Datatable with out looping, or possibly while pulling the data from the file into the datatable?

Thanks!