Merkez bankasından döviz kurlarını çekmek

Merkez bankasından döviz kurlarını çekmek için gerekli procedure aşağıdaki gibidir. 

go
   sp_configure 'show advanced options' , 1
go
   Reconfigure with Override
go
   sp_configure 'Ole Automation Procedures' , 1
go
   Reconfigure with Override
Go
 if not exists (select * from sys.tables where name = N'DOVIZKURLARI' and type = 'U')
   begin
       Create table DOVIZKURLARI (Tarih date,
                                  CrossOrder Smallint,
                                  Kod NVarchar(5),
                                  CurrencyCode NVarchar(5),
                                  UNIT  varchar(50),
                                  Isim varchar(100),
                                  CurrencyName varchar(100) ,
                                  ForexBuying float  ,
                                  ForexSelling float,
                                  BanknoteBuying float,
                                  BanknoteSelling float)
   end
GO
  if exists (select * from sys.objects where type = 'P' AND name = 'UPR_GetDovizKurlari_MerkezBankasi')
        drop procedure UPR_GetDovizKurlari_MerkezBankasi
go
   Create proc [dbo].[UPR_GetDovizKurlari_MerkezBankasi]
            (@pYil Smallint, @pAy TinyInt, @pGun TinyInt)
   As
   begin
       Declare @url as varchar(8000)
       /*
         Set @url = 'https://www.tcmb.gov.tr/kurlar/today.xml'
         Set @url = 'https://www.tcmb.gov.tr/kurlar/201903/12032019.xml'
       */

	   IF NOT EXISTS(select * from DOVIZKURLARI where tarih = DateFromParts(@pYil, @pAy, @pGun))
	   BEGIN 
       Declare @XmlYilAy NVarchar(6), @XmlTarih NVarchar(10)
       Set @XmlYilAy =  Right('0000' + cast(@pYil as varchar(4)) , 4) + Right('00' + cast(@pAy as varchar(2)) , 2)
       Set @XmlTarih =  Right('00' + cast(@pGun as varchar(2)) , 2) + Right('00' + cast(@pAy as varchar(2)) , 2) + Right('0000' + cast(@pYil as varchar(4)) , 4)

       If DateFromParts(@pYil, @pAy, @pGun) = DateAdd(dd,0,DateDiff(dd,0,GetDate())) --gelen parametrelergünün tarihi ise
           Set @url =  'https://www.tcmb.gov.tr/kurlar/today.xml'
       else
           Set @url =  'https://www.tcmb.gov.tr/kurlar/' + @XmlYilAy + '/' + @XmlTarih + '.xml'
       Print @url

       declare @OBJ AS INT
       declare @RESULT AS INT
       EXEC @RESULT = SP_OACREATE 'MSXML2.XMLHTTP', @OBJ OUT
       EXEC @RESULT = SP_OAMethod @OBJ , 'open' , null , 'GET', @url, false
       EXEC @RESULT = SP_OAMethod @OBJ, send, NULL,''
       
        If OBJECT_ID('tempdb..#XML') IS NOT Null DROP TABLE #XML

       Create table #XML ( STRXML varchar(max))
       Insert INTO #XML(STRXML) EXEC @RESULT = SP_OAGetProperty @OBJ, 'responseXML.xml'
       
       --Select * From #XML

       DECLARE @XML AS XML
       SELECT @XML = STRXML FROM #XML
       DROP TABLE #XML
       DECLARE @HDOC AS INT
       EXEC SP_XML_PREPAREDOCUMENT @HDOC OUTPUT , @XML
       
       Delete from DOVIZKURLARI where tarih = DateFromParts(@pYil, @pAy, @pGun)
       INSERT INTO DOVIZKURLARI ( Tarih,CrossOrder,Kod,CurrencyCode,UNIT,Isim,CurrencyName,ForexBuying,ForexSelling,BanknoteBuying,BanknoteSelling)
 
       SELECT DateFromParts(@pYil, @pAy, @pGun) As Tarih,
              * FROM OPENXML(@HDOC, 'Tarih_Date/Currency')
                       With (CrossOrder NVarchar(5), Kod Varchar(5),  CurrencyCode NVarchar(5),
                             Unit varchar(50) 'Unit',
                             Isim varchar(100)   'Isim',
                             CurrencyName varchar(100)   'CurrencyName',
                             ForexBuying float   'ForexBuying',
                             ForexSelling float 'ForexSelling',
                             BanknoteBuying float 'BanknoteBuying',
                             BanknoteSelling float 'BanknoteSelling'
                           )

END
   End
Go



declare @Yil int = (Select YEAR(GETDATE()))
declare @Ay int = (Select MONTH(GETDATE()))
declare @Gun int = (Select DAY(GETDATE()))
Exec UPR_GetDovizKurlari_MerkezBankasi   @pYil = @Yil, @pAy = @Ay,  @pGun = @Gun