JSONTOXML error

[vagelis.pertsinis]

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

[shawkins]

> 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

[vagelis.pertsinis]

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

[shawkins]

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