Would appreciate some advice on using external data with SQL Server 2005

Question

I'm a web developer with a bit of DB experience, but this one is beyond my knowledge.

We're trying to set up a new website. The data - products, prices, descriptions etc - are supplied by an external company in either .XLS, .CSV or XML format. This data is updates & distributed regularly, eg each month.

I could simply import all the data each time - the problem is we don't necessarily need all the products, so we would mark them as "unavailable" or something by creating a new field in the DB.

So, next time the data is provided, I need to import it again - but making sure it doesn't overwrite our "available" field.

My initial thought was to create 2 tables - one, "Products" which I would import the data into each time and a new table "availability" just with 2 fields - product ID and an available - yes/no field and join the tables on the product ID.

Is this a sensible approach? can XML files be read directly from SQL Server without importing?

Answer 1

You can import your data into a staging table then update the product reference data from that. The product table can hold your 'available' flag. Unfortunately you will have to import the XML data into SQL Server to load it, but SSIS has an XML reader, so you can use that to do the imports.