Transform table data into json or xml format in SAP HANA SQL Script

[former_member281438]

Hi All,

         I have requirement of converting table data into json or xml format using SQL Script. If it is possible please paste the sample code.

Thanks,
Venkatesh

[pfefferf · Accepted solution]

Hello Venkatesh,

SQLScript does not provide out of the box the options to convert data to JSON or XML. Of course you could implemented a simple JSON creator with imperative logic in SQLScript, but I think you will not have fun with that.

Would it be an option to use XSJS to convert your data to JSON with well-known JavaScript capabilities? You could select your data from the XSJS script using the HDB interface (e.g. JSDoc: Namespace: hdb) or calling a procedure (e.g. JSDoc: Namespace: procedures) which delivers the data and convert the it to JSON for further processing.

Best Regards,

Florian

[former_member281438 · Accepted solution]

Hello Florian,

          Yes, we can convert using XSJS or ODATA but in my scenario, i have different procedures which will be return different types of data. I would create one wrapper procedure and call the procedure dynamically. The wrapper procedure should be return data in string format because i have different types of data. 

         So i need to convert the table data into some string format and return the string from wrapper procedure. Then i can create XSJS or ODATA service on the wrapper procedure. Please help me if it is possible.

Thanks,

Venkatesh

[pfefferf · Accepted solution]

Hello Venkatesh,

by using the new HDB interface a procedure is represented as an object in XSJS. The results of the procedure are returned as JSON. With that options different usages would be possible for the different cases. In case your different procedures just have one result parameter you use the result in a static way, in case your procedures have different result interfaces you can analyze the returned objects regarding the available properties.

Following a really simple example of an XSJS which calls a procedure which returns entries in an output parameter ET_ACTIVE_OBJECT. The result is returned from the XSJS directly as JSON string. You see that the XSJS does not have a specific structured type matching the result of the procedure.

var oConnection = $.hdb.getConnection();

var fnProcedure = oConnection.loadProcedure("DEMO", "demo::test_proc");

var oResult = fnProcedure();

$.response.contentType = "application/json; charset=utf-8";
$.response.setBody(JSON.stringify(oResult.ET_ACTIVE_OBJECT));

Regards,

Florian

[former_member182302]

You could use ODATA

Regards,

Krishna Tangudu