Web Service and XML in NAV/D365 Business Central

0 Shares

Introduction

In this post I demonstrate how you can get XML data from a Web Service and then process the XML in NAV in order insert the needed values into a table.

I made the example using a service provided by the National Bank of Romania. This Web Service, gives Exchange Rates in XML format and I think it’s a good example for learning/practice purposes because it does not require a key and the XML is not too complex:

If we select, for example, the values for Year 2018, we get the Exchange Rates in the following XML format:

What we want to achieve

We want this data in NAV in a tabular format therefore let’s create code that calls this web service and gets the XML in NAV, into an XML Document DotNet variable. Then, process each “Cube” node and each “Rate” element in order to insert data into a NAV table like this:

How to do this ?

–> Because we have standard Nav Codeunit 1297 “Http Web Request Mgt”, we can easily call the web service and get the XML in NAV first into an InStream:

UpdateExchangeRates()
BaseUrl := 'http://www.bnr.ro/files/xml/years/nbrfxrates2018.xml';

HttpWebRequestMgt.Initialize(BaseUrl);
HttpWebRequestMgt.DisableUI;
HttpWebRequestMgt.SetMethod('GET');
HttpWebRequestMgt.SetReturnType('application/xml');

TempBlob.INIT;
TempBlob.Blob.CREATEINSTREAM(Instr);

IF NOT HttpWebRequestMgt.GetResponse(Instr,HttpStatusCode,ResponseHeaders) THEN
  ERROR('An error occured');

–> Then, we need to use an XMLTextReader to not do Namespaces Support. This is done because otherwise we cannot navigate through the elements of the XML using XPath expressions…
–> After this, we load the XML data into an XML Document Dot Net Variable:

IF ISNULL(XmlDoc) THEN
  XmlDoc := XmlDoc.XmlDocument;

IF ISNULL(XMLTR) THEN
  XMLTR := XMLTR.XmlTextReader(Instr);
XMLTR.Namespaces(FALSE);
XmlDoc.Load(XMLTR);

–> When this is done, we can navigate through the XML elements with XPath and get the values we’re interested in:

OriginalCurr := XmlDoc.SelectSingleNode('//Body/OrigCurrency').InnerText; //get reference currency: RON

Window.OPEN(ImportDataText + '@1@@@@@@@@@@@@@@@@@@@@@@@@@\');

XMLNodeList1 := XmlDoc.SelectNodes('//Body/Cube');
REPEAT //read each Cube node
  Window.UPDATE(1,ROUND(NodeNo / XMLNodeList1.Count * 10000,1));
  XMLNode1 := XMLNodeList1.Item(NodeNo);
  XMLAttributes := XMLNode1.Attributes;
  CurrDate := GetDateFromString(XMLAttributes.GetNamedItem('date').Value); //get Date of Exchange Rate
  FOREACH XMLElement IN XMLNode1 DO BEGIN //read each Rate from current Cube
    Multiplier := 0;
    XMLAttributes := XMLElement.Attributes;
    Currency := XMLAttributes.GetNamedItem('currency').Value;
    IF NOT ISNULL(XMLAttributes.GetNamedItem('multiplier')) THEN
      EVALUATE(Multiplier,XMLAttributes.GetNamedItem('multiplier').Value);
    EVALUATE(ExchangeRate,CONVERTSTR(XMLElement.InnerText,'.',GetSystemDecimalSeparator)); //get exchange rate and replace decimal separator with the one the current system has
    InsertExchangeRate(CurrDate,OriginalCurr,Currency,Multiplier,ExchangeRate);
  END;
  NodeNo += 1;
UNTIL NodeNo = XMLNodeList1.Count;

Window.CLOSE;
MESSAGE('Update Completed');

–> I also used some helper functions because I had to Get the Date of Currency, Get the System Decimal Separator and Insert the values into the NAV Table:

LOCAL InsertExchangeRate(DateCurr : Date;OrigCurr : Code[10];Currency : Code[10];Multiplier : Decimal;Rate : Decimal)
IF ExchangeRateRo.GET(DateCurr,OrigCurr,Currency) THEN
  EXIT;
ExchangeRateRo.INIT;
ExchangeRateRo.Date := DateCurr;
ExchangeRateRo.OrigCurrency := OrigCurr;
ExchangeRateRo.Currency := Currency;
ExchangeRateRo.Rate := Rate;
ExchangeRateRo.Multiplier := Multiplier;
ExchangeRateRo.INSERT(TRUE);

LOCAL GetDateFromString(DateAsString : Text) : Date
EVALUATE(Year,COPYSTR(DateAsString,1,4));
EVALUATE(Month,COPYSTR(DateAsString,6,2));
EVALUATE(Day,COPYSTR(DateAsString,9,2));
EXIT(DMY2DATE(Day,Month,Year));

LOCAL GetSystemDecimalSeparator() : Text
MyDecimal := 10000.25;
MyDecimalAsText := FORMAT(MyDecimal);
EXIT(FORMAT(MyDecimalAsText[7]));

–> Variables Used: