Introduction
In this post I will explain a simple way to get XML and JSON Response from Web Service into Business Central Wave 2 database.
Let’s use the Google Maps API to get the distance and driving time between 2 locations. We will send to the API 2 addresses: origin and destination. As a result, the API can respond in XML and JSON formats (depending on a parameter we specify). Then, we import the responses in both formats into Business Central Wave 2 database.
If you want to test this example yourself, first step is to create a Google Maps API key. For instance, you can follow steps from this video.
Sample code to import XML Response:
For demo purpose, I use the following 2 addresses:
Code to Call Google Maps API and import XML response result:
codeunit 50000 "Google Maps Service" { trigger OnRun() begin ImportXML('https://maps.googleapis.com/maps/api/distancematrix/xml? origins=57,+Ion+Heliade Radulescu,+Campina,+Prahova,+Romania&destinations= 11,+Episcopul Vulcan,+Bucharest,+Bucharest,+Romania&mode=driving&key=YOURAPIKEY'); end; procedure ImportXML(Url: Text): Text; var HttpClient: HttpClient; Response: HttpResponseMessage; ResponseString: Text; XmlDoc: XmlDocument; TempBlob: Codeunit "Temp Blob"; OutStr: OutStream; XmlStream: InStream; begin XMLBuffer.DeleteAll(); HttpClient.Get(Url, Response); If not Response.IsSuccessStatusCode() then Exit(StrSubstNo('Status code: %1\' + 'Description: %2', Response.HttpStatusCode(), Response.ReasonPhrase())); Response.Content.ReadAs(ResponseString); XmlDocument.ReadFrom(ResponseString, XmlDoc); TempBlob.CreateOutStream(OutStr); XmlDoc.WriteTo(OutStr); TempBlob.CreateInStream(XmlStream); XMLBuffer.LoadFromStream(XmlStream); end; var XMLBuffer: Record "XML Buffer"; }
As you can see, in this example I am using ‘real’ table “XML Buffer”. In real life you would probably use this table as temporary.. But I want to show the imported data in Business Central database. In version Wave 2 we cannot run table anymore (as we don’t have old good Development Environment), so let’s check the data in SQL:
In addition, notice 1 thing:
* In BC Wave 2 we cannot use anymore the “Temp Blob” record directly. Instead, we use codeunit “Temp Blob”.
Sample code to import JSON Response:
This step is not a lot different compared to importing the Result of web service call into XML format. To get the response as JSON we need to specify in the URL “…distancematrix/json…” instead of “…distancematrix/xml…”
codeunit 50001 "Google Maps Service2" { trigger OnRun() begin ImportXML('https://maps.googleapis.com/maps/api/distancematrix/json? origins=57,+Ion+Heliade Radulescu,+Campina,+Prahova,+Romania&destinations= 11,+Episcopul Vulcan,+Bucharest,+Bucharest,+Romania&mode=driving&key=YOURAPIKEY'); end; procedure ImportXML(Url: Text): Text; var HttpClient: HttpClient; Response: HttpResponseMessage; ResponseString: Text; begin JsonBufferReal.DeleteAll(); HttpClient.Get(Url, Response); If not Response.IsSuccessStatusCode() then Exit(StrSubstNo('Status code: %1\' + 'Description: %2', Response.HttpStatusCode(), Response.ReasonPhrase())); Response.Content.ReadAs(ResponseString); JsonBuffer.ReadFromText(ResponseString); If JsonBuffer.FindSet() then repeat JsonBufferReal := JsonBuffer; JsonBufferReal.Insert(); until JsonBuffer.Next = 0; end; var JsonBuffer: Record "Json Buffer" Temporary; JsonBufferReal: Record "Json Buffer"; }
Record “JSON Buffer” can only be used as temporary for reading data into it. However, in order to show data in SQL, I just copy it into “JSON Buffer” ‘Real’ table after it was imported into temporary table. Data in SQL looks like this:
Conclusions
* You just saw how easy it is now to get XML and JSON Response from Web Service into Business Central Wave 2 database.
* This approach can be easily used when the Web Service response is not too complex.
* In order to use the data imported into the Buffer tables, you can: filter and find records based on path or loop through all records and based on conditions retrieve the needed data from memory and use it where you need.
* Based on the simple code you saw above, we can create more complex application that uses this distance for example to ship an item to customer from the location that has the closest address to the customer address.