Hello SCN,
Hope you are all doing great. Most of the existing web API's interact via JSON format but there would be still some legacy applications which can return only XML response. In this blog , I would like to share my thoughts around using XS destinations for internet connectivity and capturing the XML response and loading the same into HANA with the help of SAX XML Parser.
Scenario :
We shall take the familiar Google Maps API and let us see how can we connect from SAP HANA and capture the XML response. We would need to define xshttpdest ( more details : SAP HANA Cloud Platform ) for outbound connectivity.
Step 1 : setting up XS destinations for internet connectivity
google.xshttpdest
host = "maps.googleapis.com";
port = 80;
pathPrefix = "/maps/api/distancematrix/xml";
proxyType = none;
authType = none;
useSSL = false;
timeout = 30000;
Once the destination file is created, we can consume this in a .xsjs file to connect to Google Maps API as shown below :
xmlparsing.xsjs
var destination_package = "TEST.test"; //Both XSJS and XSHTTPDEST file should be kept in the same package
var destination_name = "google";
var dest = $.net.http.readDestination(destination_package, destination_name);
var req = new $.web.WebRequest($.net.http.GET, "?origins=Delhi&destinations=Visakhapatnam&mode=driving&language=en-US&sensor=false");
//hard coding the origin and destination city
var client = new $.net.http.Client();
client.request(req, dest);
var response = client.getResponse();
var xml = response.body.asString();
$.response.status = $.net.http.OK;
We can now see the XML response being captured from HANA as shown below :
As seen above, we were able to capture the response and get the distance from New Delhi to Vishakapatnam :smile:
Please note that if the outbound connection you are trying to connect is a http(s) link, then you can refer to the below blog series on how to setup the trust store to access the same. Outbound httpS with HANA XS (part 3) - call the https outbound service in XS server side JavaScript ...
Step 2 : Using SAX Class for Parsing XML response :
In the same xsjs file, we will be using the methods mentioned here JSDoc: Class: SAXParser to parse the XML response we have received . While parsing we will try to format it into JSON as it will make the data load into HANA easier .
PS: There can be XML to JSON converters available in HANA, am yet to understand them so for now am doing a blunt way of formatting them into desired JSON format as shown below.
var parser = new $.util.SAXParser();
var char="";
var endtagname="";
var valuecount=0;
var textcount=0;
var data_string = '';
parser.characterDataHandler = function(s) {
char = s; // To Capture the character data into a local variable
};
parser.endElementHandler = function(name) {
endtagname = name;
// Extracting the required data from XML Response and formatting the same in JSON format
if (endtagname === 'origin_address' )
{
data_string += '\{' + '"'+ "Distance" + '"' + ':' + '\[' + '\{'+ '"' + endtagname + '"' + ':' + '"' + char +'"' + ',';
}
if (endtagname === 'destination_address' )
{
data_string += '"' + endtagname + '"' + ':' + '"' + char +'"' + ',';
}
if (endtagname === 'value' )
{
valuecount += 1;
if (valuecount === 1)
{
data_string += '"' + 'duration_value_in_secs' + '"' + ':' + '"' + char +'"' + ',';
}
else
{
data_string += '"' + 'distance_value_in_mts' + '"' + ':' + '"' + char +'"' + ',';
}
}
if (endtagname === 'text' )
{
textcount += 1;
if (textcount === 1)
{
data_string += '"' + 'duration_value_in_days' + '"' + ':' + '"' + char +'"' + ',';
}
else
{
data_string += '"' + 'distance_value_in_kms' + '"' + ':' + '"' + char +'"' + '\}';
} }
if (endtagname === 'row')
{
data_string += '\]' + '\}';
}
};
parser.parse(xml);
Once we execute the above script, you would be able to get the JSON equivalent as shown below :
Step 3 : Load the captured JSON into a table in HANA
As we now have a JSON, it is relatively easier now to load it into HANA. We need to do the below :
a) Create a table type with the structure similar to JSON format
b) Create a stored procedure with the input parameter to accept the JSON and to insert the data into a physical table in HANA
Please find the code snippets below :
Table : GOOGLE_API_DISTANCE
CREATE TABLE GOOGLE_API_DISTANCE
("origin_address" varchar(1000),"destination_address" varchar(1000),"duration_value_in_secs" varchar(1000),
"duration_value_in_days" varchar(1000),"distance_value_in_mts" varchar(1000),"distance_value_in_kms" varchar(1000))
Table Type :
CREATE TYPE TT_GOOGLE_API_DISTANCE AS TABLE
("origin_address" varchar(1000),"destination_address" varchar(1000),"duration_value_in_secs" varchar(1000),
"duration_value_in_days" varchar(1000),"distance_value_in_mts" varchar(1000),"distance_value_in_kms" varchar(1000))
Note : You could use .hdbdd file to create the table and table type . For demo purpose I have created like above
CREATE PROCEDURE SCHEMA_NAME. "GOOGLE_DISTANCE_PROC" (IN DATA CSC_CORE.TT_GOOGLE_API_DISTANCE,OUT MESSAGE VARCHAR(50) )
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER AS
BEGIN
INSERT INTO GOOGLE_API_DISTANCE (SELECT * FROM :DATA);
MESSAGE := 'Data loaded Successfully into SAP HANA';
END;
Note : Here again you can use .hdbprocedure
Now let us add the relevant code required to connect and call the procedure via xsjs.
var conn = $.hdb.getConnection();
var data = data_string;
var parse_json = JSON.parse(data);
var json_object = {};
json_object = parse_json;
var google_dist_data = json_object.Distance;
//Call procedure with the correct table structure similar to JSON
var prep_procedure = conn.loadProcedure("SCHEMA_NAME","GOOGLE_DISTANCE_PROC");
//Passing the data structure and capturing the return message
var return_message = prep_procedure(google_dist_data);
//Passing output to response
$.response.status = $.net.http.OK;
$.response.contentType = "application/json";
$.response.setBody(JSON.stringify(return_message));
//Committing and closing the connection
conn.commit();
conn.close();
Now once we execute the entire .xsjs file, we can see the below response :
Now we can see the data loaded into HANA as shown below :
Hope this blog helps in understanding XML parsing capabilities in SAP HANA.
Few points to note below :
- This xsjs file written here is static ( the origin and destination was hardcoded in the code for demo purpose.
- We would be getting XML to JSON conversion capabilities in the future version , which would help in loading the data into HANA
- Definately if the sequence of the columns change, than it would definately effect the script.
- This blog is of purely personal opinion , Would welcome to correct my the process mentioned above to make it more efficient
Thanks for your time in reading this ,do let me know your feedback on this.
Yours,
Krishna Tangudu :smile:
- SAP Managed Tags:
