One of the standard requests that comes with every SAP implementation is automatic download of exchange rates to your system. Today I will show you on an example of Central Bank Of Turkey, how to transform XML from bank site into internal table, which can be then used inside your code to populate values of BAPI_EXCHANGERATE_CREATE FM.
There are few ways to download the XML to your ABAP code, I prefer to create an RFC destination (lets call it here Z_DESTINATION_TO_TCMB), as on many servers you need to provide credentials for PROXY and it is safer to keep it saved inside RFC destination, than in your ABAP code in plain text.
So lets assume you have an RFC destination that points to https://tcmb.gov.tr/kurlar/today.xml. It will look like this:
The XML behind has following structure:
<?xml version="1.0" encoding="UTF-8"?>
<?xml-stylesheet type="text/xsl" href="/isokur.xsl"?>
<Tarih_Date Tarih="06.04.2020" Date="04/06/2020" Bulten_No="2020/68" >
<Currency CrossOrder="0" Kod="USD" CurrencyCode="USD">
<Unit>1</Unit>
<Isim>ABD DOLARI</Isim>
<CurrencyName>US DOLLAR</CurrencyName>
<ForexBuying>6.7549</ForexBuying>
<ForexSelling>6.7671</ForexSelling>
<BanknoteBuying>6.7502</BanknoteBuying>
<BanknoteSelling>6.7773</BanknoteSelling>
<CrossRateUSD/>
<CrossRateOther/>
</Currency>
<Currency CrossOrder="1" Kod="AUD" CurrencyCode="AUD">
<Unit>1</Unit>
<Isim>AVUSTRALYA DOLARI</Isim>
<CurrencyName>AUSTRALIAN DOLLAR</CurrencyName>
<ForexBuying>4.0841</ForexBuying>
<ForexSelling>4.1108</ForexSelling>
<BanknoteBuying>4.0654</BanknoteBuying>
<BanknoteSelling>4.1354</BanknoteSelling>
<CrossRateUSD>1.6501</CrossRateUSD>
<CrossRateOther/>
</Currency>
<Currency CrossOrder="2" Kod="DKK" CurrencyCode="DKK">
<Unit>1</Unit>
<Isim>DANİMARKA KRONU</Isim>
<CurrencyName>DANISH KRONE</CurrencyName>
<ForexBuying>0.97584</ForexBuying>
<ForexSelling>0.98063</ForexSelling>
<BanknoteBuying>0.97515</BanknoteBuying>
<BanknoteSelling>0.98289</BanknoteSelling>
<CrossRateUSD>6.9115</CrossRateUSD>
<CrossRateOther/>
</Currency>
<Currency CrossOrder="9" Kod="EUR" CurrencyCode="EUR">
<Unit>1</Unit>
<Isim>EURO</Isim>
<CurrencyName>EURO</CurrencyName>
<ForexBuying>7.2990</ForexBuying>
<ForexSelling>7.3121</ForexSelling>
<BanknoteBuying>7.2939</BanknoteBuying>
<BanknoteSelling>7.3231</BanknoteSelling>
<CrossRateUSD/>
<CrossRateOther>1.0805</CrossRateOther>
</Currency>
<Currency CrossOrder="10" Kod="GBP" CurrencyCode="GBP">
<Unit>1</Unit>
<Isim>İNGİLİZ STERLİNİ</Isim>
<CurrencyName>POUND STERLING</CurrencyName>
<ForexBuying>8.2941</ForexBuying>
<ForexSelling>8.3373</ForexSelling>
<BanknoteBuying>8.2883</BanknoteBuying>
<BanknoteSelling>8.3498</BanknoteSelling>
<CrossRateUSD/>
<CrossRateOther>1.2300</CrossRateOther>
</Currency>
<Currency CrossOrder="3" Kod="CHF" CurrencyCode="CHF">
<Unit>1</Unit>
<Isim>İSVİÇRE FRANGI</Isim>
<CurrencyName>SWISS FRANK</CurrencyName>
<ForexBuying>6.8935</ForexBuying>
<ForexSelling>6.9378</ForexSelling>
<BanknoteBuying>6.8832</BanknoteBuying>
<BanknoteSelling>6.9482</BanknoteSelling>
<CrossRateUSD>0.9776</CrossRateUSD>
<CrossRateOther/>
</Currency>
<Currency CrossOrder="4" Kod="SEK" CurrencyCode="SEK">
<Unit>1</Unit>
<Isim>İSVEÇ KRONU</Isim>
<CurrencyName>SWEDISH KRONA</CurrencyName>
<ForexBuying>0.66188</ForexBuying>
<ForexSelling>0.66873</ForexSelling>
<BanknoteBuying>0.66141</BanknoteBuying>
<BanknoteSelling>0.67027</BanknoteSelling>
<CrossRateUSD>10.16</CrossRateUSD>
<CrossRateOther/>
</Currency>
<Currency CrossOrder="6" Kod="CAD" CurrencyCode="CAD">
<Unit>1</Unit>
<Isim>KANADA DOLARI</Isim>
<CurrencyName>CANADIAN DOLLAR</CurrencyName>
<ForexBuying>4.7765</ForexBuying>
<ForexSelling>4.7980</ForexSelling>
<BanknoteBuying>4.7588</BanknoteBuying>
<BanknoteSelling>4.8162</BanknoteSelling>
<CrossRateUSD>1.4123</CrossRateUSD>
<CrossRateOther/>
</Currency>
<Currency CrossOrder="11" Kod="KWD" CurrencyCode="KWD">
<Unit>1</Unit>
<Isim>KUVEYT DİNARI</Isim>
<CurrencyName>KUWAITI DINAR</CurrencyName>
<ForexBuying>21.7240</ForexBuying>
<ForexSelling>22.0083</ForexSelling>
<BanknoteBuying>21.3982</BanknoteBuying>
<BanknoteSelling>22.3384</BanknoteSelling>
<CrossRateUSD/>
<CrossRateOther>3.2342</CrossRateOther>
</Currency>
<Currency CrossOrder="7" Kod="NOK" CurrencyCode="NOK">
<Unit>1</Unit>
<Isim>NORVEÇ KRONU</Isim>
<CurrencyName>NORWEGIAN KRONE</CurrencyName>
<ForexBuying>0.64160</ForexBuying>
<ForexSelling>0.64591</ForexSelling>
<BanknoteBuying>0.64115</BanknoteBuying>
<BanknoteSelling>0.64739</BanknoteSelling>
<CrossRateUSD>10.50</CrossRateUSD>
<CrossRateOther/>
</Currency>
<Currency CrossOrder="8" Kod="SAR" CurrencyCode="SAR">
<Unit>1</Unit>
<Isim>SUUDİ ARABİSTAN RİYALİ</Isim>
<CurrencyName>SAUDI RIYAL</CurrencyName>
<ForexBuying>1.7958</ForexBuying>
<ForexSelling>1.7990</ForexSelling>
<BanknoteBuying>1.7823</BanknoteBuying>
<BanknoteSelling>1.8125</BanknoteSelling>
<CrossRateUSD>3.7615</CrossRateUSD>
<CrossRateOther/>
</Currency>
<Currency CrossOrder="5" Kod="JPY" CurrencyCode="JPY">
<Unit>100</Unit>
<Isim>JAPON YENİ</Isim>
<CurrencyName>JAPENESE YEN</CurrencyName>
<ForexBuying>6.1745</ForexBuying>
<ForexSelling>6.2154</ForexSelling>
<BanknoteBuying>6.1517</BanknoteBuying>
<BanknoteSelling>6.2390</BanknoteSelling>
<CrossRateUSD>109.14</CrossRateUSD>
<CrossRateOther/>
</Currency>
<Currency CrossOrder="12" Kod="BGN" CurrencyCode="BGN">
<Unit>1</Unit>
<Isim>BULGAR LEVASI</Isim>
<CurrencyName>BULGARIAN LEV</CurrencyName>
<ForexBuying>3.7110</ForexBuying>
<ForexSelling>3.7596</ForexSelling>
<BanknoteBuying></BanknoteBuying>
<BanknoteSelling></BanknoteSelling>
<CrossRateUSD>1.8100</CrossRateUSD>
<CrossRateOther/>
</Currency>
<Currency CrossOrder="13" Kod="RON" CurrencyCode="RON">
<Unit>1</Unit>
<Isim>RUMEN LEYİ</Isim>
<CurrencyName>NEW LEU</CurrencyName>
<ForexBuying>1.5024</ForexBuying>
<ForexSelling>1.5221</ForexSelling>
<BanknoteBuying></BanknoteBuying>
<BanknoteSelling></BanknoteSelling>
<CrossRateUSD>4.4709</CrossRateUSD>
<CrossRateOther/>
</Currency>
<Currency CrossOrder="14" Kod="RUB" CurrencyCode="RUB">
<Unit>1</Unit>
<Isim>RUS RUBLESİ</Isim>
<CurrencyName>RUSSIAN ROUBLE</CurrencyName>
<ForexBuying>0.08786</ForexBuying>
<ForexSelling>0.08901</ForexSelling>
<BanknoteBuying></BanknoteBuying>
<BanknoteSelling></BanknoteSelling>
<CrossRateUSD>76.46</CrossRateUSD>
<CrossRateOther/>
</Currency>
<Currency CrossOrder="15" Kod="IRR" CurrencyCode="IRR">
<Unit>100</Unit>
<Isim>İRAN RİYALİ</Isim>
<CurrencyName>IRANIAN RIAL</CurrencyName>
<ForexBuying>0.01599</ForexBuying>
<ForexSelling>0.01620</ForexSelling>
<BanknoteBuying></BanknoteBuying>
<BanknoteSelling></BanknoteSelling>
<CrossRateUSD>42000</CrossRateUSD>
<CrossRateOther/>
</Currency>
<Currency CrossOrder="16" Kod="CNY" CurrencyCode="CNY">
<Unit>1</Unit>
<Isim>ÇİN YUANI</Isim>
<CurrencyName>CHINESE RENMINBI</CurrencyName>
<ForexBuying>0.94703</ForexBuying>
<ForexSelling>0.95943</ForexSelling>
<BanknoteBuying></BanknoteBuying>
<BanknoteSelling></BanknoteSelling>
<CrossRateUSD>7.0928</CrossRateUSD>
<CrossRateOther/>
</Currency>
<Currency CrossOrder="17" Kod="PKR" CurrencyCode="PKR">
<Unit>1</Unit>
<Isim>PAKİSTAN RUPİSİ</Isim>
<CurrencyName>PAKISTANI RUPEE</CurrencyName>
<ForexBuying>0.04016</ForexBuying>
<ForexSelling>0.04069</ForexSelling>
<BanknoteBuying></BanknoteBuying>
<BanknoteSelling></BanknoteSelling>
<CrossRateUSD>167.26</CrossRateUSD>
<CrossRateOther/>
</Currency>
<Currency CrossOrder="18" Kod="QAR" CurrencyCode="QAR">
<Unit>1</Unit>
<Isim>KATAR RİYALİ</Isim>
<CurrencyName>QATARI RIAL</CurrencyName>
<ForexBuying>1.8321</ForexBuying>
<ForexSelling>1.8561</ForexSelling>
<BanknoteBuying></BanknoteBuying>
<BanknoteSelling></BanknoteSelling>
<CrossRateUSD>3.6663</CrossRateUSD>
<CrossRateOther/>
</Currency>
<Currency CrossOrder="0" Kod="XDR" CurrencyCode="XDR">
<Unit>1</Unit>
<Isim>ÖZEL ÇEKME HAKKI (SDR) </Isim>
<CurrencyName>SPECIAL DRAWING RIGHT (SDR) </CurrencyName>
<ForexBuying>9.1853</ForexBuying>
<ForexSelling/>
<BanknoteBuying/>
<BanknoteSelling/>
<CrossRateUSD/>
<CrossRateOther>1.35856</CrossRateOther>
</Currency>
</Tarih_Date>
Now there are two main ways to convert XML, you can parse XML using for example CL_XML_DOCUMENT class, or you can create a transformation that will convert the XML into internal table.
I've choosen the second option in this example, as I've never done XSLT transformation before. I've ended up with transformation Z_TCMB_TRANSFORMATION that you can find bellow.
<xsl:transform xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:sap="http://www.sap.com/sapxsl" version="1.0">
<xsl:strip-space elements="*"/>
<xsl:template match="/">
<asx:abap xmlns:asx="http://www.sap.com/abapxml" version="1.0">
<asx:values>
<EXCHANGE_RATES>
<IMPORT_FILE>
<xsl:for-each select="/Tarih_Date">
<TARIH>
<xsl:value-of select="@Tarih"/>
</TARIH>
<DATE>
<xsl:value-of select="@Date"/>
</DATE>
<BULTEN_NO>
<xsl:value-of select="@Bulten_No"/>
</BULTEN_NO>
<CURRENCIES>
<xsl:for-each select="/Tarih_Date/Currency">
<CURRENCY>
<CROSS_ORDER>
<xsl:value-of select="@CrossOrder"/>
</CROSS_ORDER>
<KOD>
<xsl:value-of select="@Kod"/>
</KOD>
<CURRENCY_CODE>
<xsl:value-of select="@CurrencyCode"/>
</CURRENCY_CODE>
<UNIT>
<xsl:value-of select="Unit"/>
</UNIT>
<ISIM>
<xsl:value-of select="Isim"/>
</ISIM>
<CURRENCY_NAME>
<xsl:value-of select="CurrencyName"/>
</CURRENCY_NAME>
<FOREX_BUYING>
<xsl:value-of select="ForexBuying"/>
</FOREX_BUYING>
<FOREX_SELLING>
<xsl:value-of select="ForexSelling"/>
</FOREX_SELLING>
<BANKNOTE_BUYING>
<xsl:value-of select="BanknoteBuying"/>
</BANKNOTE_BUYING>
<BANKNOTE_SELLING>
<xsl:value-of select="BanknoteSelling"/>
</BANKNOTE_SELLING>
<CROSS_RATE_USD>
<xsl:value-of select="CrossRateUSD"/>
</CROSS_RATE_USD>
<CROSS_RATE_OTHER>
<xsl:value-of select="CrossRateOther"/>
</CROSS_RATE_OTHER>
</CURRENCY>
</xsl:for-each>
</CURRENCIES>
</xsl:for-each>
</IMPORT_FILE>
</EXCHANGE_RATES>
</asx:values>
</asx:abap>
</xsl:template>
</xsl:transform>
To download the XML file and to call transformation, you can use following dirty ABAP code. The structure of the XML will be transformed into internal table of a type EXCHANGE_RATES.
PUBLIC SECTION.
TYPES: BEGIN OF currency,
cross_order TYPE string,
kod TYPE string,
currency_code TYPE string,
unit TYPE string,
isim TYPE string,
currency_name TYPE string,
forex_buying TYPE string,
forex_selling TYPE string,
banknote_buying TYPE string,
banknote_selling TYPE string,
cross_rate_usd TYPE string,
cross_rate_other TYPE string,
END OF currency,
BEGIN OF exchange_rate,
tarih TYPE string,
date TYPE string,
bulten_no TYPE string,
currencies TYPE STANDARD TABLE OF currency WITH DEFAULT KEY,
END OF exchange_rate,
exchange_rates TYPE STANDARD TABLE OF exchange_rate WITH DEFAULT KEY.
METHODS: get_exchange_rates RETURNING VALUE(exchange_rates) TYPE exchange_rates.
PRIVATE SECTION.
DATA: xml TYPE string.
METHODS: get_xml_from_bank.
METHODS: transform_xml_to_table RETURNING VALUE(exchange_rates) TYPE exchange_rates.
ENDCLASS.
CLASS lcl_exchange_rates IMPLEMENTATION.
METHOD get_xml_from_bank.
cl_http_client=>create_by_destination(
EXPORTING
destination = 'Z_DESTINATION_TO_TCMB'
IMPORTING
client = DATA(client)
EXCEPTIONS
argument_not_found = 1
destination_not_found = 2
destination_no_authority = 3
plugin_not_active = 4
internal_error = 5
OTHERS = 6
).
IF sy-subrc EQ 0.
client->request->set_method( if_http_request=>co_request_method_get ).
client->send( EXCEPTIONS
http_communication_failure = 1
http_invalid_state = 2
http_processing_failed = 3
http_invalid_timeout = 4
OTHERS = 5
).
IF sy-subrc EQ 0.
client->receive(
EXCEPTIONS
http_communication_failure = 1
http_invalid_state = 2
http_processing_failed = 3
OTHERS = 4 ).
IF sy-subrc EQ 0.
xml = client->response->get_cdata( ).
ENDIF.
ENDIF.
ENDIF.
ENDMETHOD.
METHOD transform_xml_to_table.
CALL TRANSFORMATION z_tcmb_transformation
SOURCE XML xml
RESULT exchange_rates = exchange_rates.
ENDMETHOD.
METHOD get_exchange_rates.
get_xml_from_bank( ).
exchange_rates = transform_xml_to_table( ).
ENDMETHOD.
ENDCLASS.
Once you have your internal table filled, you can prepare the code to use BAPI_EXCHANGERATE_CREATE.
