How can I take USD Forex Buying and EUR Forex Buying Rates with date from TCMB, using SQL and XML?-CodePudding

I want to take USD and EUR Rates from TCMB page.

Im using this first of all.

Use Coruscant 
go 

   sp_configure 'show advanced options' , 1
go
   Reconfigure with Override
go
   sp_configure 'Ole Automation Procedures' , 1
go
   Reconfigure with Override
Go

Im using this for creating table.

 Use Coruscant
go
-- Döviz Kurlarının Yazılacağı Tablo oluşturuluyor
   if not exists (select * from sys.tables where name = N'DVZKRLR' and type = 'U')
   begin
       Create table DVZKRLR (Tarih date, DolarForexBuying float,EuroForexBuying float)
                           
   end
Go

Im using this for parse XML file but I couldn't parse correctly. I want to take only Date(as Tarih), DolarForexBuying,EuroForexBuying rates.

  if exists (select * from sys.objects where type = 'P' AND name = 'UPR_GetDovizKurlari_frm_MerkezBankasi')
  

          drop procedure UPR_GetDovizKurlari_MerkezBankasi
    go
       Create proc [dbo].[UPR_GetDovizKurlari_frm_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'
       */

       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 DVZKRLR where tarih = DateFromParts(@pYil, @pAy, @pGun)
       INSERT INTO DVZKRLR ( Tarih,DolarForexBuying,EuroForexBuying)
 
       SELECT DateFromParts(@pYil, @pAy, @pGun) As Tarih,
              * FROM OPENXML(@HDOC, 'Tarih_Date/Currency')
              --I WANT TO TAKE ONLY DOLAR FOREX BUYING AND EURO FOREX BUYING RATE WITH DATE..
              --I COULDN'T PARSE XML FILE
                       With (CrossOrder NVarchar(5), CurrencyCode NVarchar(5),ForexBuying float   'ForexBuying')
   End
Go

And Im using this code for adding rates to my table.

 USE CORUSCANT 
DECLARE @SAYAC INT = 1
WHILE @SAYAC <= 31
BEGIN

Exec UPR_GetDovizKurlari_MerkezBankasi @pYil = 2022, -- smallint
                                          @pAy = 1,  -- tinyint
                                          @pGun = @SAYAC  -- tinyint
                                         
  SET @SAYAC = @SAYAC   1
END

CodePudding user response：

You can move the whole XML document into an xml variable and use values function:

declare @MyXmlVar xml;
select @MyXmlVar=strxml from #XML
select [email protected]('/Tarih_Date[1]/Currency[@Kod="USD"][1]/ForexBuying[1]', 'float'),
[email protected]('/Tarih_Date[1]/Currency[@Kod="EUR"][1]/ForexBuying[1]', 'float')

Here,the #XML is your temp table holding the XML document in a varchar column.

You can also convert the whole XML into a table, first, and then select, like:

select cast(strxml as xml) as XmlCol into #XmlTbl from  #XML;
with CurrencyBuyRates as (
    SELECT 
        Currencies.Currency.value('@Kod', 'varchar(3)')  as Kod
      , Currencies.Currency.value('./ForexBuying[1]', 'float') as ForexBuying  
    FROM #XMLTbl
    CROSS APPLY xmlCol.nodes('/Tarih_Date/Currency') AS Currencies(Currency) 
) 
select  
*
from CurrencyBuyRates
where Kod in ('USD','EUR')

Iyi sanslar!

CodePudding user response：

Microsoft proprietary OPENXML and its companions sp_xml_preparedocument and sp_xml_removedocument are kept just for backward compatibility with the obsolete SQL Server 2000. Their use is diminished just to very few fringe cases.

Starting from SQL Server 2005 onwards, it is strongly recommended to re-write your SQL and switch it to XQuery.

Notable points:

Tarih column is DATE datatype
DolarForexBuying and *EuroForexBuying * columns should use DECIMAL(10,4) datatype. FLOAT data type is used for imprecise numbers like PI 3.14159.....

SQL

DECLARE @xml XML = 
'<?xml version="1.0" encoding="UTF-8"?>
<?xml-stylesheet type="text/xsl" href="isokur.xsl"?>
<Tarih_Date Tarih="18.02.2022" Date="02/18/2022" Bulten_No="2022/35">
    <Currency CrossOrder="0" Kod="USD" CurrencyCode="USD">
        <Unit>1</Unit>
        <Isim>ABD DOLARI</Isim>
        <CurrencyName>US DOLLAR</CurrencyName>
        <ForexBuying>13.6118</ForexBuying>
        <ForexSelling>13.6363</ForexSelling>
        <BanknoteBuying>13.6022</BanknoteBuying>
        <BanknoteSelling>13.6567</BanknoteSelling>
        <CrossRateUSD/>
        <CrossRateOther/>
    </Currency>
    <Currency CrossOrder="1" Kod="AUD" CurrencyCode="AUD">
        <Unit>1</Unit>
        <Isim>AVUSTRALYA DOLARI</Isim>
        <CurrencyName>AUSTRALIAN DOLLAR</CurrencyName>
        <ForexBuying>9.7935</ForexBuying>
        <ForexSelling>9.8573</ForexSelling>
        <BanknoteBuying>9.7484</BanknoteBuying>
        <BanknoteSelling>9.9165</BanknoteSelling>
        <CrossRateUSD>1.3866</CrossRateUSD>
        <CrossRateOther/>
    </Currency>
    <Currency CrossOrder="2" Kod="DKK" CurrencyCode="DKK">
        <Unit>1</Unit>
        <Isim>DANİMARKA KRONU</Isim>
        <CurrencyName>DANISH KRONE</CurrencyName>
        <ForexBuying>2.0770</ForexBuying>
        <ForexSelling>2.0872</ForexSelling>
        <BanknoteBuying>2.0756</BanknoteBuying>
        <BanknoteSelling>2.0920</BanknoteSelling>
        <CrossRateUSD>6.5433</CrossRateUSD>
        <CrossRateOther/>
    </Currency>
    <Currency CrossOrder="9" Kod="EUR" CurrencyCode="EUR">
        <Unit>1</Unit>
        <Isim>EURO</Isim>
        <CurrencyName>EURO</CurrencyName>
        <ForexBuying>15.4745</ForexBuying>
        <ForexSelling>15.5024</ForexSelling>
        <BanknoteBuying>15.4637</BanknoteBuying>
        <BanknoteSelling>15.5257</BanknoteSelling>
        <CrossRateUSD/>
        <CrossRateOther>1.1369</CrossRateOther>
    </Currency>
...
</Tarih_Date>';

-- INSRT INTO DVZKRLR  -- uncomment when you are ready
SELECT TRY_CONVERT(DATE, @xml.value('(/Tarih_Date/@Tarih)[1]', 'CHAR(10)'),104) AS Tarih
    , @xml.value('(/Tarih_Date/Currency[@Kod="USD"]/ForexBuying/text())[1]', 'DECIMAL(10,4)') AS DolarForexBuying 
    , @xml.value('(/Tarih_Date/Currency[@Kod="EUR"]/ForexBuying/text())[1]', 'DECIMAL(10,4)') AS EuroForexBuying

Output

 ------------ ------------------ ----------------- 
|   Tarih    | DolarForexBuying | EuroForexBuying |
 ------------ ------------------ ----------------- 
| 2022-02-18 |          13.6118 |         15.4745 |
 ------------ ------------------ -----------------