SQL Server: Import XML from web-server?

Question

Is there support in SQL Server for loading/reading/transferring/importing/fetching/inserting XML, directly from a web-server, into a table?

Pretend i want to fetch XML from a web-server, such as:

• exchange rates published by the Bank of Canada
• a web-site's current sitemap
• an rss feed

SQL Server 2005 (and newer) has native support for XML data types. In the last few months the internet has become really popular. The ability to find information on the internet has the potential to become useful and important.

Does SQL Server support such a thing?

Or do i have to use (pseudo-code):

XmlHttpRequest xml = new XmlHttpRequest("http://treasury.gov/ExchangeRates.xml");
SqlServerConnection conn = new SqlServerConnection("neptune", "sa", "password");
conn.Execute("INSERT INTO Exchange Rates (RatesXml) VALUES (%1)", xml.ResponseStream);

---

Edit One: Since Windows is able to make opening files over http:

http://newegg.com/api/HardDrivePrices.xml

as transparent as opening files off the local hard drive:

c:\Windows\Temp\HardDrivePrices.xml

i was hoping SQL Server could have the ability to load XML from a file. Then i simply replace the filename with the filenameUrl, e.g.:

CREATE TABLE docs (pk INT PRIMARY KEY, xCol XML not null)

INSERT INTO docs 
SELECT 10, xCol
FROM    (SELECT * FROM OPENROWSET 
      (BULK 'http://www.bankofcanada.ca/stat/fx-xml.xml',
      SINGLE_BLOB) AS xCol) AS R(xCol)

Except this fails with:

Cannot bulk load because the file "http://www.bankofcanada.ca/stat/fx-xml.xml" could not be opened. Operating system error code 123(The filename, directory name, or volume label syntax is incorrect.).

Answer 1

This article describes how to consume a webservice from SQL Server 2005+: http://www.databasejournal.com/features/mssql/article.php/3821271/Calling-a-Web-Service-from-within-SQL-Server.htm

Answer 2

I don't know of a means of performing this in vanilla T-SQL, you might get some mileage by creating a CLR function to download content.