Return json content using webservice connection

[lunendl]

Hi Everyone,

I'm new to your product and I'm loving it. However there is one little issue I'm facing which I'm struggling to solve.(It's been a week now) I'm connecting to a webservice with input as JSON and returns JSON content:
Input Parameters query : string(4000) is:
{"queryType":"groupBy","dataSource":"wikipedia","granularity":"minute","dimensions":[ "page" ],"aggregations":[ {"type":"count", "name":"rows"}, {"type":"longSum", "fieldName":"count", "name":"edit_count"}], "filter":{ "type":"selector", "dimension":"namespace", "value":"article" }, "intervals":[ "2014-10-02T10:00/2014-10-03T00" ]}

My SQL Result is:

select * from ( exec "DataProviderView1"."runQueryJson"({"queryType":"groupBy","dataSource":"wikipedia","granularity":"minute","dimensions":[ "page" ],"aggregations":[   {"type":"count", "name":"rows"},   {"type":"longSum", "fieldName":"count", "name":"edit_count"}], "filter":{ "type":"selector", "dimension":"namespace", "value":"article" }, "intervals":[ "2014-10-02T10:00/2014-10-03T00" ]}) ) AS X_X

org.teiid.runtime.client.TeiidClientException: java.lang.RuntimeException: Remote org.teiid.api.exception.query.QueryParserException: TEIID31100 Parsing error: Encountered "exec \"DataProviderView1\".\"runQueryJson\"([*]{[*]\"queryType\":" at line 1, column 57.

Was expecting: "char" | "cast" | "convert" | "any" | "array_agg" | "case" | "exists" | "false" | "hour" | "insert" ...

I have tested my steps by using an existing webservice called "CurrencyConvertor" from http://webservicex.net/CurrencyConvertor.asmx?WSDL and it works without any issues.

My Setup steps are(I have attached the WSDL file):
Teiid Import - "Web Service Source >> Source and View Model (SOAP)"

Connection Profile - File From: http://demo.company.uk.com/company_webservice/DataProvider.asmx?WSDL

                              - End Point: DataProviderSoap
                              - Binding Type: SOAP11

Service Mode: PAYLOAD

WSDL Selection Details:

Operation: runQueryJson

  binding: DataProviderSoap

  port: DataProviderSoap

  service: DataProvider

  id: {http://demo.intenda.uk.com/fraxses_webservice/}DataProviderSoap.runQueryJson

  input message: runQueryJsonSoapIn

  output message: runQueryJsonSoapOut

  fault names: none

Procedure Definition:
- Request
    Body - Schema Contents:
                  -runQueryJson
                     -sequence
                        -query

                            -string
               Element Info:
                  query

- Response

Body - Schema Contents:
                  -runQueryJsonResponse
                     -sequence
                        -runQueryJsonResult

                            -string
               Column Info:
                  Root Path - /tns:runQueryJsonResponse
                  Grid:             
                        Name: runQueryJsonResult, Data Type: string, Path: /tns:runQueryJsonResult

If anyone can help in any way, I would be sooooo grateful.

[shawkins]

> I'm new to your product and I'm loving it.

Excellent.  Great to hear things are going well for a new user.

As for the parser exception, Teiid does not accept a literal JSON, rather it must be specified as just a string literal in single quotes:

exec "DataProviderView1"."runQueryJson"('{"...'

[lunendl]

Thank you Steve! I got it working by using double quotes for some reason:"{"queryType":"groupBy","dataSource":"wikipedia","granularity":"minute","dimensions":[ "page" ],"aggregations":[   {"type":"count", "name":"rows"},   {"type":"longSum", "fieldName":"count", "name":"edit_count"}], "filter":{ "type":"selector", "dimension":"namespace", "value":"article" }, "intervals":[ "2014-10-06T10:59/2014-10-06T11" ]}"

I am now trying to convert the JSON response to XML.

My original response view query is:

BEGIN

  SELECT t.* FROM XMLTABLE(XMLNAMESPACES('http://tempuri.org/' AS tns), '/tns:runQuery1Response' PASSING Teiid_FRX_View.runQuery1_response.xml_in COLUMNS runQuery1Result string PATH '/tns:runQuery1Result') AS t;

END

I've tried the following(JSONTOXML) but it does not like the format:

BEGIN

  SELECT t.* FROM XMLTABLE(XMLNAMESPACES('http://tempuri.org/' AS tns), '/tns:runQuery1Response' PASSING JSONTOXML('tns:runQuery1Response', Teiid_FRX_View.runQuery1_response.xml_in) COLUMNS runQuery1Result string PATH '/tns:runQuery1Result') AS t;

END

ERROR: TEIID30070 The function 'JSONTOXML('tns:runQuery1Response', Teiid_FRX_View.runQuery1_response.xml_in)' is a valid function form, but the arguments do not match a known type signature and cannot be converted using implicit type conversions.

Thanks again!

[shawkins]

Continued on XML data (type as VARCHAR) to XML Table(true XML)