Importing Unicode XML Code SQL

• tibetors

  SSC Journeyman

  Points: 79

  More actions

  October 9, 2017 at 4:28 pm

  #100959

  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!

• Eirikur Eiriksson

  SSC Guru

  Points: 182943

  More actions

  October 31, 2017 at 2:20 pm

  #1966071

  any luck so far?