SAP HANA: Using SAX Parser for Loading XML response from outbound HTTP into table

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 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 :

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 :

Source: scn.sap.com