3 Replies Latest reply on Mar 27, 2015 12:43 PM by Steven Hawkins

    JSONTOXML error

    Vagelis Pertsinis Newbie

      Hi,

       

      I'm having difficulties with parsing the JSON request from our API and accessing the data. The typical response from our API is

      :

       

      {

          "batchId": "4485aabb13e54f23a2df7e2685f4b06d",

          "results": [

              {

                  "requestId": "ac04922ef14641bfa3a0593301a79389",

                  "values": [

                      [

                          {

                              "columnId": 17,

                              "value": "BLG001"

                          },

                          {

                              "columnId": 18,

                              "value": "Sydney Bennelong Plaza"

                          },

                          {

                              "columnId": 43,

                              "value": "Retail"

                          },

                          {

                              "columnId": -2,

                              "value": 40813

                          },

                          {

                              "columnId": -1,

                              "value": 0

                          }

                      ],

                      [

                          {

                              "columnId": 17,

                              "value": "BLG002"

                          },

                          {

                              "columnId": 18,

                              "value": "Sydney George St Store"

                          },

                          {

                              "columnId": 43,

                              "value": "Retail"

                          },

                          {

                              "columnId": -2,

                              "value": 40814

                          },

                          {

                              "columnId": -1,

                              "value": 1

                          }

                      ]

                  ],

                  "error": null

              }

          ]

      }

       

       

      This creates the following error in Teiid:

       

      org.teiid.runtime.client.TeiidClientException: java.lang.RuntimeException: Remote org.teiid.core.TeiidProcessingException: TEIID30171 Unexpected multi-valued result was returned for XMLTable column "columnId".  Path expressions for non-XML type columns should return at most a single result.

       

      The xpath used to get the columnId field is:

       

      ... COLUMNS columnId string PATH '/results/values//columnId/text()') AS A;

       

      I kind of understand the difficulty in parsing the response after it gets converted to XML, I'm just wondering if there is a workaround.

       

      Cheers

       

      Vagos

        • 1. Re: JSONTOXML error
          Steven Hawkins Master

          > This creates the following error in Teiid:

           

          Which means that the path evaluates to a sequence with multiple values.  XMLTable expects a single value.

           

          the JSONTOXML logic is giving you a document structure that looks something like:

           

          <root>

             <results>

                 <values>

                    <columnId>...

                    <value>...

                 <values>

                 <values>...

           

          When in doubt just preview the results from running JSONTOXML to see the document structure.

           

          So when you give it a path like '/results/values//columnId/text()' there are multiple columnId values that match that at the given context.  You need your context expression (the first argument to xmltable) to express what you are iterating on - which should be a sequence.  This would look like:

           

          XMLTABLE('/root/results/values' passing doc COLUMNS columnId string PATH 'columnId/text()') AS A

          • 2. Re: JSONTOXML error
            Vagelis Pertsinis Newbie

            You rock mate! Last question, is there an easy way to flatten the response? In this case I'm getting back two columns[1] but it's not easy to identify which rows link to which object.

             

             

             

            [1]

            columnId      value

            17               test1

            18               lorem ispum

            -2                    0

            -1                   test

            17               test2

            18               lorem ipsum 2

            -2               1

            -1                test2

            • 3. Re: JSONTOXML error
              Steven Hawkins Master

              How would you identify which object?


              XMLTABLE('/root/results/values' passing doc COLUMNS columnId string PATH 'columnId/text()' requestId string PATH '../requestId/text()'


              Would put the requestId with each row.


              You can also use a FOR ORDINALITY column - FROM Clause - Teiid 8.11 (draft) - Project Documentation Editor