JSONTOXML

[mlopezal]

Hi,

Do you have some example on how to use JSONTOXML in transformation ¿?.

I've tried to used it in on view model to call a REST service but I have some errors, I think that in datatypes of the function parameter:

CREATE VIRTUAL PROCEDURE

BEGIN

          DECLARE string VARIABLES.INPUT_ENDPOINT;

          VARIABLES.INPUT_ENDPOINT = (('?query=' || FreebaseLocation.Location.query) || '&domain=Location&indent=true=');

          VARIABLES.INPUT_ENDPOINT = REPLACE(VARIABLES.INPUT_ENDPOINT, ' ', '%20');

          SELECT t.* FROM JSONTOXML('Resultados', (EXEC FreebaseLocation.invoke(binding => 'HTTP', action => 'GET', endpoint => VARIABLES.INPUT_ENDPOINT))) AS x,

XMLTABLE('/Resultados/result' PASSING result COLUMNS mid string PATH 'mid', name string PATH 'name', notable_name string PATH 'notable/name', notable_id string PATH 'notable/id', lang string PATH 'language', score double PATH 'score') AS t;

END

Thanks a lot

[shawkins]

JSONTOXML is a scalar function and cannot currently be used as a table function.  You will need to change your SQL:

SELECT t.* FROM (EXEC FreebaseLocation.invoke(binding => 'HTTP', action => 'GET', endpoint => VARIABLES.INPUT_ENDPOINT)) AS x, XMLTABLE('/Resultados/result' PASSING JSONTOXML('Resultados', result) COLUMNS mid string PATH 'mid', name string PATH 'name', notable_name string PATH 'notable/name', notable_id string PATH 'notable/id', lang string PATH 'language', score double PATH 'score') AS t

You can check the Reference for examples on json input to xml mapping.  You can also just issue a query to verify what your document looks like:

SELECT JSONTOXML('Resultados', result) FROM (EXEC FreebaseLocation.invoke(binding => 'HTTP', action => 'GET', endpoint => <end point>)) AS x

Steve

[mlopezal]

Thanks Steven.

I continue having problems with this function.

First I try to  execute a query to check what the service return with this function

select * from (call "FreebaseLocation".invoke(binding=>'HTTP', action=>'GET', endpoint=>'?query=Palencia&domain=Location&indent=true')) as x;

but I obtain an error saying that unexpected character {, but the service return JSON, how can I do this test?

By other hand when I try to use JSON function:

SELECT JSONTOXML('Resultados', result) from (EXEC "FreebaseLocation".invoke(binding=>'HTTP', action=>'GET', endpoint=>'?query=Palencia&domain=Location&indent=true')) as x; I obtain an error saying that is a valid function form, but the arguments do not match a known type signature and cannot be converted using implicit type conversions.

What's wrong?

Thanks in advance,

Mónica

[shawkins]

Mónica,

I should have noticed this before, the invoke procedure against the ws translator will return an XML result.  If you are retrieving a JSON result, you'll want to use invokeHttp instead, which will return a blob of the response.  You should then be able to pass the blob directly to the JSONTOXML function as it knows how to detect the character set (as long as it is one of the standard UTF 8/16/32 charsets).

Steve