1 Reply Latest reply on Jul 5, 2016 3:44 PM by Ramesh Reddy

    parsing json response having jsonarr

    dineshnasa Newbie

      I am having a json structure like

      {

      response:{

      docs:[{

      test1:"SOME_VALUE1",

      test2:"SOME_VALUE2"

      },

      {

      test1:"SOME_VALUE3",

      test2:"SOME_VALUE4"

      }

       

      }

       

      I am using the following

      Create VIEW TestView (test1 varchar(255), test1 varchar(255))

      as

      select A.test1, A.test2

      from (EXEC MySource.invokeHttp('GET', null, 'testURL', 'TRUE')) as f

      XMLTABLE('/RESPONSE/test' passing JSONTOXML('response', f.result) COLUMNS test1 string path 'response/docs/test1', test2 string path 'response/docs/test2') as A.


      I am getting error

      Error:TEIID30171:Unexpected multi valued result was returned for XMLTABLE column test1

        • 1. Re: parsing json response having jsonarr
          Ramesh Reddy Master

          Your JSON document is is wrong, not formatted correctly. Missing quotes around names, array closing, closing brace etc. Make sure that is correct. Once corrected a query like below

           

          select test1, test2 from XMLTABLE('/response/response/docs' passing  
               JSONTOXML('response', '{"response":{"docs":[{"test1":"SOME_VALUE1","test2":"SOME_VALUE2"},{"test1":"SOME_VALUE3","test2":"SOME_VALUE4"}]}}')
               COLUMNS test1 string path 'test1', test2 string path 'test2') as A
          

           

          gives back

           

          test1test2
          SOME_VALUE1SOME_VALUE2
          SOME_VALUE3SOME_VALUE4
          1 of 1 people found this helpful