RESTful web service error: Unexpected multi-valued result

[sarin_ashish]

Hi,

I created a VDB that accessed the RESTful web services located here: http://www.thomas-bayer.com/sqlrest/CUSTOMER/

The steps I followed to create the source and view models are:

1. Selected the option Import -> File Source(XML) >> Source and View Model

2. Selected XML file via remote URL in the XML Import File Options dialog box

3. In the XML Data File Source Selection dialog box, I created a new Data File Source by selecting the REST Web Services URL option and entered the http://www.thomas-bayer.com/sqlrest/CUSTOMER/ URL

4. In XML Data File Import Options dialog box, XML File Content section shows CustomerList -> Customer -> href. I selected the Customer as the column name.

5. In the View Model Definition dialog box, i kept the name of the view table as new_table.

When I executed the query, select * from new_table, I got the following error:

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

What is the reason for getting such an error. I was expecting that the select * from new_table would have returned text contained inside all the <Customer> elements.

regards

ashish

[rareddy]

Ashish,

This typically means that your root where you are starting is higher level. Change the XML root to "/CUSTOMERList/CUSTOMER" and give it a try.

SELECT
  A.CUSTOMER AS CUSTOMER
  FROM
  (EXEC Customer.invokeHttp('GET', null, 'http://www.thomas-bayer.com/sqlrest/CUSTOMER/', 'TRUE')) AS f,
XMLTABLE(XMLNAMESPACES('http://www.w3.org/1999/xlink' AS xlink), '/CUSTOMERList/CUSTOMER' PASSING XMLPARSE(DOCUMENT f.result) COLUMNS CUSTOMER string PATH 'text()') AS A

Ramesh..

[sarin_ashish]

It didn't work; i got the same exception again. Can you also let me know how Teiid deals with RESTful web services that return JSON or may be simple text?

regards

ashish

[rareddy]

You must be doing something else wrong then, I copied above from my working copy from your endpoint where I could get results fine. I attached my Desinger project to this thread.

If you need to deal with JSON, use function JSONTOXML, to convert the results to XML then use XMLTABLE construct to read contents out. Check the documentation on JSONTOXML.

Ramesh..

[shawkins]

With the sql that Ramesh provided, it's not possible to get that exception.  The message is indicating that from once the column expression is evaluated relative to the root xquery you are getting a sequence back, not a single value.  For example with the doc:

{code}
<a>
  <b x="1"/>
  <b x="2"/>
</a>
{code}

The xmltable:

{code}
XMLTABLE('/a' PASSING doc COLUMNS x string PATH 'b/@x')
{code}

Will throw an exception because you are saying "for each a make the the string column value for x to b/@x" - however for the root a, there are two values for the attribute x.

Where as:

{code}
XMLTABLE('/a/b' PASSING doc COLUMNS x string PATH '@x')
{code}

Will be fine because the context is now the b element which does have a single x attribute value.  You can even do things like selecting a single element out of the children if that is more appropriate:

{code}
XMLTABLE('/a' PASSING doc COLUMNS x string PATH 'b[1]/@x')
{code}

> Can you also let me know how Teiid deals with RESTful web services that return JSON or may be simple text?

JSON is primarily handled through the JSONToXML function, with the resultant XML then processed in xmltable.  Simple text presumably means something structured which would be processed by texttable as delimited or fixed width content.

[sarin_ashish]

Thanks. Is there any documentation which talks about how to consume a RESTful web service that returns JSON. The Import -> File Source(XML) >> Source and View Model option only allows us to consume a RESTful web service that returns XML.

regards

ashish

[shawkins]

Moved to Designer as that has been the context for your questions.  You can see more on JSONTOXML at JSON Functions - Teiid 8.8 (draft) - Project Documentation Editor but you'll want someone from Designer to weigh in on what design time aids may be available.

[rareddy]

You can see the discussion about this in the community https://community.jboss.org/message/870385