Loading data from xml http stream

[markjerz]

Hi,

I have a problem which I don't entirely know how to tackle:

Essentially, is it possible to query a web service (via http), using
sql server 2000, and then import that data in to the database?

I have seen many posts on openxml and sql servers bulk load facilities
but nobody seems to mention whether you can open an http stream and
read the xml in from there.

Any help would be greatly appreciated.

Thanks.

[Plamen Ratchev]

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

[Jack Vamvas]

Further to the process Plamen outlined , for step 1 , I used a HTTP file
downloader i.e Lynx, which allowed me to add a list of XML files.

If you are using SQL 2000 and OPENXML , you will have problems. If this is
the case , check http://www.quicksqlserver.com/2007/0...l_more_th.html

--

Jack Vamvas
___________________________________
Need an IT job? http://www.ITjobfeed.com/sql

<a href="http://www.itjobfeed.com/sql"sql it jobs</a>

<ma******@googlemail.comwrote in message
news:11**********************@p77g2000hsh.googlegr oups.com...

Hi,

I have a problem which I don't entirely know how to tackle:

Essentially, is it possible to query a web service (via http), using
sql server 2000, and then import that data in to the database?

I have seen many posts on openxml and sql servers bulk load facilities
but nobody seems to mention whether you can open an http stream and
read the xml in from there.

Any help would be greatly appreciated.

Thanks.

[Plamen Ratchev]

"Jack Vamvas" <DE**********@del.comwrote in message
news:_s******************************@bt.com...

>
If you are using SQL 2000 and OPENXML , you will have problems. If this is
the case , check
http://www.quicksqlserver.com/2007/0...l_more_th.html

This is why I had to pass the XML content to the SP as NTEXT. That removes
the limitation of 8000 characters if trying to read the XML file directly
from SQL Server 2000. But the method in the link that you provided works
too, if direct read from a file is needed.

Plamen Ratchev
http://www.SQLStudio.com