3 Replies Latest reply on Dec 28, 2011 8:58 AM by Steven Hawkins

    JSONTOXML

    Mónica L.A. Newbie

      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

        • 1. Re: JSONTOXML
          Steven Hawkins Master

          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
            Mónica L.A. Newbie

            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
              Steven Hawkins Master

              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