我想從 TCMB 頁面獲取美元和歐元匯率。
我首先使用這個。
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
我用它來創建表。
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
我使用它來決議 XML 檔案，但我無法正確決議。我只想采用 Date(as Tarih)、DolarForexBuying、EuroForexBuying 匯率。
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
我使用此代碼將費率添加到我的表中。
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
uj5u.com熱心網友回復：
Microsoft 專有OPENXML及其伙伴sp_xml_preparedocument，sp_xml_removedocument保留只是為了與過時的 SQL Server 2000 向后兼容。它們的使用減少到極少數的邊緣情況。
從 SQL Server 2005 開始，強烈建議重新撰寫 SQL 并將其切換到 XQuery。
值得注意的點：
- Tarih列是
DATE資料型別 - DolarForexBuying和 *EuroForexBuying * 列應使用
DECIMAL(10,4)資料型別。FLOAT 資料型別用于不精確的數字，例如 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>';
-- INSERT 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
輸出
------------ ------------------ -----------------
| Tarih | DolarForexBuying | EuroForexBuying |
------------ ------------------ -----------------
| 2022-02-18 | 13.6118 | 15.4745 |
------------ ------------------ -----------------
uj5u.com熱心網友回復：
您可以將整個 XML 檔案移動到 xml 變數中并使用 values函式：
declare @MyXmlVar xml;
select @MyXmlVar=strxml from #XML
select USDBuyRate=@MyXmlVar.value('/Tarih_Date[1]/Currency[@Kod="USD"][1]/ForexBuying[1]', 'float'),
EURBuyRate=@MyXmlVar.value('/Tarih_Date[1]/Currency[@Kod="EUR"][1]/ForexBuying[1]', 'float')
在這里，#XML 是您在 varchar 列中保存 XML 檔案的臨時表。
您還可以先將整個 XML 轉換為表格，然后再選擇，例如：
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')
伊伊桑斯拉爾！
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/430488.html
上一篇：在PHP中將字串轉換為xml
下一篇：從回傳的XML字串中獲取屬性