-
1. Re: JSONTOXML
shawkins Nov 30, 2011 3:36 PM (in response to mlopezal)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
-
2. Re: JSONTOXML
mlopezal Dec 27, 2011 12:44 PM (in response to shawkins)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
-
3. Re: JSONTOXML
shawkins Dec 28, 2011 8:58 AM (in response to mlopezal)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