October 9, 2017 at 4:28 pm
I am trying to import XML data from the following webpage: http://www.tcmb.gov.tr/kurlar/today.xml. This is a continuously updating webpage and I want my SQL code to obtain the latest values.
If I manually download the xml file to my computer and run my SQL code, it works without any issues:
DECLARE @xmlFile XML
SET @xmlFile = (SELECT * FROM OPENROWSET(BULK 'C:\Users\sqlfreaq\Desktop\today.xml', SINGLE_CLOB) AS xmldata)
SELECT @xmlFile.value('(Tarih_Date/Currency/ForexSelling)[1]', 'decimal(18,5)') AS DatabaseID
However, when I try to use OLE stored procedures to import data, some of the Unicode characters change and as a result, the XML cannot be parsed. My code is as follows:
DECLARE @url VARCHAR(300),
@win INT,
@hr INT,
@xml xml
SET @url = 'http://www.tcmb.gov.tr/kurlar/today.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
Create table #tmp(dt nvarchar(max))
insert into #tmp
exec @hr =sp_OAGetProperty @win, 'ResponseText'
Select
CAST(CAST([dt] AS VARCHAR(MAX)) AS XML) TT
from #tmp -- single column/single row.
Drop Table #tmp -- clean up
EXEC @hr = sp_OADestroy @win
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win
However, if I use this code to get data from a NON-UNICODE xml file (just change the website in the code from http://www.tcmb.gov.tr/kurlar/today.xml to http://www.bnr.ro/nbrfxrates.xml, it works. How, can I modify my code or method, so that I can use the online XML file.
Thanks a lot!
October 31, 2017 at 2:20 pm
any luck so far?
😎
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply