3

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:

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.).

Ian Boyd
  • 246,734
  • 253
  • 869
  • 1,219

2 Answers2

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

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • Correct me if i'm wrong, but it looks like this requires Visual Studio. – Ian Boyd Aug 19 '10 at 11:22
  • @Ian it does require VS. I'm new to all this and I can't say for sure, but my thoughts are that SQL Server is not able to do it on its own and so MS evolved an external way to doing that, due to security issues it would raise if SQL would do it. Here, this is not a new request at all: http://stackoverflow.com/questions/180046/sql-server-using-clr-integration-to-consume-a-web-service – cregox Oct 10 '10 at 13:38
  • @Cawas, it does not require VS. VS is nor external way of doing that. SQL Server is not browser, it is DBMS (DataBase Mgmt System) – Gennady Vanin Геннадий Ванин Oct 12 '10 at 13:05
0

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.

Will A
  • 24,780
  • 5
  • 50
  • 61