(SQL Server) Magyar Nemzeti Bank (MNB) Get Currencies

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 ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

// 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
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @rest int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Rest', @rest OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int

    -- 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_9_5_0.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_9_5_0.StringBuilder', @sbRequestBody OUT

    EXEC sp_OAMethod @xml, 'GetXmlSb', @success OUT, @sbRequestBody
    DECLARE @sbResponseBody int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.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_9_5_0.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>&lt;MNBCurrencies&gt;&lt;Currencies&gt;&lt;Curr&gt;HUF&lt;/Curr&gt;&lt;Curr&gt;EUR&lt;/Curr&gt;&lt;Curr&gt;AUD&lt;/Curr&gt; ... &lt;/MNBCurrencies&gt;</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_9_5_0.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_9_5_0.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