INNOVA_GUNLUK_KURU_GETIR prosedürünü joblara ekleyip sabah çaly?tyrabilirsiniz.
create PROCEDURE INNOVA_GUNLUK_KURU_GETIR
AS
BEGIN
-- RSS FEED
DECLARE @docHandle INT;
DECLARE @xmlData XML;
DECLARE @URL NVARCHAR(255);
DECLARE @file NVARCHAR(255);
DECLARE @cmd NVARCHAR(255);
DECLARE @sql NVARCHAR(255);
DECLARE @tXML TABLE(data XML);
--SET @URL = 'http://www.tcmb.gov.tr/kurlar/201507/20072015.xml';
SET @URL = 'http://www.tcmb.gov.tr/kurlar/today.xml'
SET @URL = REPLACE(@URL, '?','')
SET @file = 'D:\temp.xml';
-- Downloading the data
SET @cmd = 'C:\Windows\System32\WindowsPowerShell\v1.0\powershell (new-object System.Net.WebClient).DownloadFile( ''' + @URL + ''',''' + @file + ''' )'
EXEC master.dbo.xp_cmdshell @cmd, no_output
-- Loading the Downloaded File into the XML variable
SET @sql = 'SELECT BulkColumn FROM OPENROWSET( BULK ''' + @file + ''', SINGLE_BLOB ) AS a'
INSERT @tXML EXEC(@sql);
SELECT @xmlData = data from @tXML
-- Preparing the Relational Table from the XML variable
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlData;
DELETE FROM NETSIS..DOVIZ WHERE TARIH = CONVERT(nvarchar, GETDATE() - 1 ,102)
INSERT INTO NETSIS..DOVIZ
(TARIH, SIRA, DOV_ALIS, DOV_SATIS, EFF_ALIS, EFF_SATIS)
SELECT
CONVERT(nvarchar, cast(Tarihi as datetime),102) as Tarih, NK.SIRA
, TCMB.Alis, TCMB.Satis
, TCMB.EfAlis, TCMB.EfSatis
FROM OPENXML(@docHandle, N'//Tarih_Date/Currency')
WITH (
Tarihi VARCHAR(100) '../@Date'
,CurrencyCode VARCHAR(10) '@CurrencyCode'
,CrossOrder VARCHAR(10) '@CrossOrder'
,Unit VARCHAR(100) 'Unit'
,Isim VARCHAR(100) 'Isim'
,Alis VARCHAR(100) 'ForexBuying'
,Satis VARCHAR(100) 'ForexSelling'
,EfAlis VARCHAR(100) 'BanknoteBuying'
,EfSatis VARCHAR(100) 'BanknoteSelling'
) AS TCMB
LEFT OUTER JOIN NETSIS..KUR NK ON
(CASE WHEN NK.NETSISSIRA = 1 THEN 0 ELSE NULL END) = TCMB.CrossOrder
OR (CASE WHEN NK.NETSISSIRA = 20 THEN 9 ELSE NULL END) = TCMB.CrossOrder
where TCMB.CurrencyCode <> 'XDR'
AND NK.SIRA IS NOT NULL
;
EXEC sp_xml_removedocument @docHandle;
END
-------------
İnnova Antalya Proje Danışmanlık
Orhan ÇÖPÜROĞLU
0544 241 45 55
0242 311 45 56
|