(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
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @rest int
    -- Use "Chilkat_9_5_0.Rest" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.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
    -- Use "Chilkat_9_5_0.Xml" for versions of Chilkat < 10.0.0
    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
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbRequestBody OUT

    EXEC sp_OAMethod @xml, 'GetXmlSb', @success OUT, @sbRequestBody
    DECLARE @sbResponseBody int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    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
    -- Use "Chilkat_9_5_0.Xml" for versions of Chilkat < 10.0.0
    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>&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
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    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
    -- Use "Chilkat_9_5_0.Xml" for versions of Chilkat < 10.0.0
    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