Posted by Plamen Ratchev on 05/08/07 17:05

I had recently implement similar solution in SQL Server 2000. Here is a
simplified outline of the process:

- Write a small helper application (any language that has Internet
capabilities will do: C#, VB.NET, etc.) to connect to the Web service URL
and download the XML to a stream. In my case I had the application take all
parameters from tables in the DB (like download schedule, URL, etc.).
- Create a stored procedure that takes a parameter of data type NTEXT. Then
inside the SP use sp_xml_preparedocument and OPENXML to parse and store the
XML document into a table.
- Call the SP from your helper application passing the stream as an input
parameter. Then there are multiple ways to schedule your helper application
to run at regular intervals (in my case it was set up as service).

There are a lot of data validation, encryption, etc. steps that I am
skipping in this outline.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

• Next in forum: how to identify special characters with a single case statement
• Prev in forum: Loading data from xml http stream
• Thread view: Re: Loading data from xml http stream

[Reply to this message]