The Acme Product Company has a url that provides me with an XML file of the latest data for their products. The feed is located at:

http://www.acme.com/acmeproductfeed.

The task is to:

1. Get the daily XML product feed from their url
2. Save the XML file with today's date
3. Compare today's XML file with my existing data in SQL Server
4. Update any changes to price and stock quantity for existing products
5. Insert any new products
6. Automate the entire process so I don't need to do anything

Well, for someone who knows SQL Server and XML, it's a simple task. But, like me, if you are Googling SQL Server and XML well....

In order to automate comparing the XML feed from the url above to my existing data in SQL Server,the AcmeProductTable, I will need the following four items:

1. An XML Map of the Acme xml feed file (see MSDN link and my example above).
2. A vbs script that will get the latest xml file from the url, date the file with today's date, save the file, and then locate the latest saved file (today's) and use the file to create a table in SQL Server.
3. A stored procedure in SQL Server that will compare the table we created in in step 2 above with my AcmeProductTable and perform any required updating and inserting.
4. A simple batch file to automate all of above.

I am going to use this same folder for all of the other files below as well.

For my VBS scipt, I will use the following, which I have saved as "acmescript.vbs":

C:\Documents and Settings\Administrator\Desktop\Acme\acmescript.vbs

Blogging, like SQL Server, is not my forte, so I have used italic where portions should be quoted out of the script:

' VBScript source code to get the file from the url then stamp the file with todays date and save it to my Acme file folder.
The important bit here is inserting the +sDate+ into the name of the saved file

objADOStream.Write objXMLHTTP.ResponseBody
objADOStream.Position = 0 'Set the stream position to the start

Set objFSO = Createobject("Scripting.FileSystemObject")
If objFSO.Fileexists(strHDLocation) Then objFSO.DeleteFile strHDLocation
Set objFSO = Nothing

objADOStream.SaveToFile strHDLocation
objADOStream.Close
Set objADOStream = Nothing
End if

Set objXMLHTTP = Nothing

' Now create a staging stable - acmefeed - in my database I will use this acmefeed table to compare to my existing production table AcmeProductTable. Note - make the name of the staging table the same as the element below the ROOT element of the XML file. In my case, acmefeed. There is probably some way around this, but I could not find it.

' Now with the latest XML feed in my database in the form of a table, I can execute a stored procedure to update Price and Stock Quantity and then insert new new data into the appropriate table and then drop my acmefeed table

Below is the stored procedure, "update_acmefeed", that I am executing in the vb script above. This will compare the contents of my staging table, "acmefeed", against my actual production table AcmeProductTable. It will update any existing products where the price and/or stock quantity has changed, as well as insert any new products. I then drop the staging table (acmefeed).

CREATE PROCEDURE update_acmefeed
AS
BEGIN
-- First Update any changes to price and stock quantity for existing items
-- Then insert any new items into the AcmeProductTable
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

update AcmeProductTable set product_price=@price,product_stock_quantity=@quantity
where AcmeProductID=@id

Fetch next from curFeed into @id,@price,@quantity
end

CLOSE curFeed DEALLOCATE curFeed

insert into AcmeProductTable
select product_id, product_name,product_price,product_stock_quantity
from acmefeed A where not exists(select AcmeProductID from AcmeProductTable where AcmeProductID=a.product_id)

drop table acmefeed

END

Finally, I am going to schedule the above VBS to run daily by creating a simple batch file with the following contents: