SQL Server
SQL Server
Magyar Nemzeti Bank (MNB) Get Currencies
See more REST Misc Examples
The Magyar Nemzeti Bank (MNB) is the central bank of Hungary. In this role, its primary objective is to achieve and maintain price stability. The MNB website is available to visitors in both Hungarian and English. The MNB provides the Arfolyam (trans. Exchange Rate) API, which can be used to retrieve current and historic currency exchange rates. This service uses SOAP calls issued in XML format.This example gets a list of currencies.
Chilkat SQL Server Downloads
-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
DECLARE @hr int
DECLARE @iTmp0 int
-- Important: Do not use nvarchar(max). See the warning about using nvarchar(max).
DECLARE @sTmp0 nvarchar(4000)
DECLARE @success int
SELECT @success = 0
DECLARE @rest int
EXEC @hr = sp_OACreate 'Chilkat.Rest', @rest OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
-- URL: http://www.mnb.hu/arfolyamok.asmx
DECLARE @bTls int
SELECT @bTls = 0
DECLARE @port int
SELECT @port = 80
DECLARE @bAutoReconnect int
SELECT @bAutoReconnect = 1
EXEC sp_OAMethod @rest, 'Connect', @success OUT, 'www.mnb.hu', @port, @bTls, @bAutoReconnect
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @rest, 'ConnectFailReason', @iTmp0 OUT
PRINT 'ConnectFailReason: ' + @iTmp0
EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @rest
RETURN
END
-- --------------------------------------------------------------------------------
-- Also see Chilkat's Online WSDL Code Generator
-- to generate code and SOAP Request and Response XML for each operation in a WSDL.
-- --------------------------------------------------------------------------------
DECLARE @xml int
EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml OUT
EXEC sp_OASetProperty @xml, 'Tag', 'soapenv:Envelope'
EXEC sp_OAMethod @xml, 'AddAttribute', @success OUT, 'xmlns:soapenv', 'http://schemas.xmlsoap.org/soap/envelope/'
EXEC sp_OAMethod @xml, 'AddAttribute', @success OUT, 'xmlns:web', 'http://www.mnb.hu/webservices/'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'soapenv:Header', ''
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'soapenv:Body|web:GetCurrencies', ''
EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'Content-Type', 'text/xml'
EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'SOAPAction', '/webservices/MNBArfolyamServiceSoap/GetCurrencies'
EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'Accept', 'application/xml'
DECLARE @sbRequestBody int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbRequestBody OUT
EXEC sp_OAMethod @xml, 'GetXmlSb', @success OUT, @sbRequestBody
DECLARE @sbResponseBody int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbResponseBody OUT
EXEC sp_OAMethod @rest, 'FullRequestSb', @success OUT, 'POST', '/arfolyamok.asmx', @sbRequestBody, @sbResponseBody
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @xml
EXEC @hr = sp_OADestroy @sbRequestBody
EXEC @hr = sp_OADestroy @sbResponseBody
RETURN
END
DECLARE @respStatusCode int
EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @respStatusCode OUT
IF @respStatusCode >= 400
BEGIN
PRINT 'Response Status Code = ' + @respStatusCode
PRINT 'Response Header:'
EXEC sp_OAGetProperty @rest, 'ResponseHeader', @sTmp0 OUT
PRINT @sTmp0
PRINT 'Response Body:'
EXEC sp_OAMethod @sbResponseBody, 'GetAsString', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @xml
EXEC @hr = sp_OADestroy @sbRequestBody
EXEC @hr = sp_OADestroy @sbResponseBody
RETURN
END
PRINT 'response status code = ' + @respStatusCode
DECLARE @xmlResponse int
EXEC @hr = sp_OACreate 'Chilkat.Xml', @xmlResponse OUT
EXEC sp_OAMethod @xmlResponse, 'LoadSb', @success OUT, @sbResponseBody, 1
EXEC sp_OAMethod @xmlResponse, 'GetXml', @sTmp0 OUT
PRINT @sTmp0
-- The XML response contains this:
-- <?xml version="1.0" encoding="utf-8" ?>
-- <s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
-- <s:Body>
-- <GetCurrenciesResponse xmlns="http://www.mnb.hu/webservices/" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
-- <GetCurrenciesResult><MNBCurrencies><Currencies><Curr>HUF</Curr><Curr>EUR</Curr><Curr>AUD</Curr> ... </MNBCurrencies></GetCurrenciesResult>
-- </GetCurrenciesResponse>
-- </s:Body>
-- </s:Envelope>
-- Get the GetCurrenciesResult
DECLARE @resultEncoded nvarchar(4000)
EXEC sp_OAMethod @xmlResponse, 'GetChildContent', @resultEncoded OUT, 's:Body|GetCurrenciesResponse|GetCurrenciesResult'
PRINT @resultEncoded
-- Entity decode the result to get XML.
DECLARE @sbResult int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbResult OUT
EXEC sp_OAMethod @sbResult, 'Append', @success OUT, @resultEncoded
EXEC sp_OAMethod @sbResult, 'EntityDecode', @success OUT
-- Load it into XML.
DECLARE @xmlResult int
EXEC @hr = sp_OACreate 'Chilkat.Xml', @xmlResult OUT
EXEC sp_OAMethod @xmlResult, 'LoadSb', @success OUT, @sbResult, 1
EXEC sp_OAMethod @xmlResult, 'GetXml', @sTmp0 OUT
PRINT @sTmp0
-- The result is this:
-- <?xml version="1.0" encoding="utf-8" ?>
-- <MNBCurrencies>
-- <Currencies>
-- <Curr>HUF</Curr>
-- <Curr>EUR</Curr>
-- <Curr>AUD</Curr>
-- <Curr>BGN</Curr>
-- <Curr>BRL</Curr>
-- <Curr>CAD</Curr>
-- <Curr>CHF</Curr>
-- <Curr>CNY</Curr>
-- <Curr>CZK</Curr>
-- <Curr>DKK</Curr>
-- <Curr>GBP</Curr>
-- <Curr>HKD</Curr>
-- <Curr>HRK</Curr>
-- <Curr>IDR</Curr>
-- <Curr>ILS</Curr>
-- <Curr>INR</Curr>
-- <Curr>ISK</Curr>
-- <Curr>JPY</Curr>
-- <Curr>KRW</Curr>
-- <Curr>MXN</Curr>
-- <Curr>MYR</Curr>
-- <Curr>NOK</Curr>
-- <Curr>NZD</Curr>
-- <Curr>PHP</Curr>
-- <Curr>PLN</Curr>
-- <Curr>RON</Curr>
-- <Curr>RSD</Curr>
-- <Curr>RUB</Curr>
-- <Curr>SEK</Curr>
-- <Curr>SGD</Curr>
-- <Curr>THB</Curr>
-- <Curr>TRY</Curr>
-- <Curr>UAH</Curr>
-- <Curr>USD</Curr>
-- <Curr>ZAR</Curr>
-- <Curr>ATS</Curr>
-- <Curr>AUP</Curr>
-- <Curr>BEF</Curr>
-- <Curr>BGL</Curr>
-- <Curr>CYN</Curr>
-- <Curr>CSD</Curr>
-- <Curr>CSK</Curr>
-- <Curr>DDM</Curr>
-- <Curr>DEM</Curr>
-- <Curr>EEK</Curr>
-- <Curr>EGP</Curr>
-- <Curr>ESP</Curr>
-- <Curr>FIM</Curr>
-- <Curr>FRF</Curr>
-- <Curr>GHP</Curr>
-- <Curr>GRD</Curr>
-- <Curr>IEP</Curr>
-- <Curr>ITL</Curr>
-- <Curr>KPW</Curr>
-- <Curr>KWD</Curr>
-- <Curr>LBP</Curr>
-- <Curr>LTL</Curr>
-- <Curr>LUF</Curr>
-- <Curr>LVL</Curr>
-- <Curr>MNT</Curr>
-- <Curr>NLG</Curr>
-- <Curr>OAL</Curr>
-- <Curr>OBL</Curr>
-- <Curr>OFR</Curr>
-- <Curr>ORB</Curr>
-- <Curr>PKR</Curr>
-- <Curr>PTE</Curr>
-- <Curr>ROL</Curr>
-- <Curr>SDP</Curr>
-- <Curr>SIT</Curr>
-- <Curr>SKK</Curr>
-- <Curr>SUR</Curr>
-- <Curr>VND</Curr>
-- <Curr>XEU</Curr>
-- <Curr>XTR</Curr>
-- <Curr>YUD</Curr>
-- </Currencies>
-- </MNBCurrencies>
--
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @xml
EXEC @hr = sp_OADestroy @sbRequestBody
EXEC @hr = sp_OADestroy @sbResponseBody
EXEC @hr = sp_OADestroy @xmlResponse
EXEC @hr = sp_OADestroy @sbResult
EXEC @hr = sp_OADestroy @xmlResult
END
GO