Getting XML web page in SQL 2000

Hi All,.
I am trying to open and read an XML web page directly from SQL Server 2000.
I have found various articles on preparing and opening an XML document (http://www.perfectxml.com/articles/xml/importxmlsql.asp andhttp://techrepublic.com.com/5100-9592-5796532.html#) but both presume the XML file is local. Is there a way of getting the data direct from a web page (e.g.http://www.bnz.co.nz/xml/currencySelect.xml) in SQL code ? I know I could do it from an ASP page or through .NET, but am looking for a purely SQL based solution at the moment. If not possible, I'll go the ASP route.
Note that I do not have the option of going to SQL2005.

Many Thanks !

[1213 byte] By [Pablo_IN_NZ] at [2008-2-16]

«« system error 126
»» Calling normal stored procedures from Extended Procedures

# 1

In SQL 2000, your options for retrieving data from arbitrary URIs are pretty limited. Two options immediately come to mind:
1.) Write an extended stored proc in native code which retrieves the data from the URI. This could be tricky and in general we are trying to move people away from extended procs and towards using the CLR in the server.
2.) Have an external application (potentially CLR) which consumes the data and stores it in the database.

<SalesPitch>
FWIW, the problem is nearly trivial if you decide to move to SQL Server 2005. See my blog entry here:
http://blogs.msdn.com/jgalla/archive/2005/09/13/464881.aspx

Where I use SQLCLR and XQuery to build an RSS reader.
</SalesPitch>

-John Gallardo
SQL Server Engine

JohnGallardo-MSFT at 2007-10-6 >

top of Msdn Tech,SQL Server,SQL Server XML...

# 2

Thanks for the swift reply. Unfortunately SQL2005 is not an option, I wish it was ! Big Smile

Looks like I'll have to go with the option of getting the data from the xml page in another ASP page then saving to the SQL Database.

Pablo_IN_NZ at 2007-10-6 >

SQL Server Hot Topic

SQL Server New Topic

SQL Server

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech