Import xml file from web page in SQL

Transact-SQL
1

hi,
I want to import xml file directly from web page into microsoft sql table.
At the moment the import is done after the XML file is downloaded local.
I want to skip this step to manualy download the file.
It can be done in SQL?
when i change the path i get this error: Cannot bulk load because the file "http://www.bnr.ro/nbrfxrates.xml" could not be opened. Operating system error code 123(The filename, directory name, or volume label syntax is incorrect.)
thanx.

2

SQL Server does not come with any out-of-the box ability to consume web site data. Here is one approach:
Calling a web service from within SQL Server that uses a CLR module to do it

3

You have more option:

  • powerShell
  • SSIS - good option
  • CRL

With t-sql you can use the following script: Testat!

 DECLARE    @url VARCHAR(300),
    @win INT,
    @hr INT,
    @Text VARCHAR(4000),
    @xml xml


SET    @url = 'http://www.bnr.ro/nbrfxrates.xml'

EXEC @hr = sp_OACreate 'WinHttp.WinHttpRequest.5.1', @win OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr = sp_OAMethod @win, 'Open', NULL, 'GET', @url, 'false'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr = sp_OAMethod @win, 'Send'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr = sp_OAGetProperty @win, 'ResponseText', @Text OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr = sp_OADestroy @win 
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win 

set @xml=CAST(@Text AS XML)
select @xml
;WITH XMLNAMESPACES( 'http://www.bnr.ro/xsd' AS ns)
select t.c.value('@currency[1]','varchar(15)') as Moneda,
      t.c.value('.','varchar(15)') as Curs,
      t.c.value('../@date[1]','datetime') as DataCurs
from @xml.nodes('/ns:DataSet/ns:Body/ns:Cube/ns:Rate') as t(c)
where  t.c.value('@currency[1]','varchar(15)') in ('CHF','EUR','GBP','USD')

The output will look like this:
Moneda Curs DataCurs
CHF 4.2773 2015-06-12 00:00:00.000
EUR 4.4696 2015-06-12 00:00:00.000
GBP 6.1987 2015-06-12 00:00:00.000
USD 4.0002 2015-06-12 00:00:00.000

4

i get this result:
Error Source Description HelpFile HelpID
0x80072EFD WinHttp.WinHttpRequest A connection with the server could not be established NULL 0

Error Source Description HelpID HelpFile
0x8000000A WinHttp.WinHttpRequest The data necessary to complete this operation is not yet available. 0 NULL

thank you

5

You need to change some configuration. Ole Automation Procedures.
If this is allow, then next script it is doing just that.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO