Unable to invoke a REST service using Teiid Designer

[tanmoypalit]

Hello everyone,

I am trying to consume a REST Service using Teiid Designer. I have used the following document as a reference: https://access.redhat.com/knowledge/techbriefs/consume-rest-based-web-service-relational-model

My web service output is like this:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<collection>

  <wellHeader>

    <well_ID>test123</well_ID>

    <well_Name>NewWell_123</well_Name>

    <well_TD>0.0</well_TD>

    <well_Field>ABCD</well_Field>

    <well_Operator>Peter</well_Operator>

    <well_Location_LAT>58.482166</well_Location_LAT>

    <well_Location_LONG>0.966556</well_Location_LONG>

    <well_Country_ID>2080</well_Country_ID>

    <well_Depth_Reference>Rotary table amsl</well_Depth_Reference>

    <well_UWBI>79876568767</well_UWBI>

    <well_Block>block1</well_Block>

    <well_Datum>EY887</well_Datum>

  </wellHeader>

  <wellHeader>

    <well_ID>test1231_1</well_ID>

    <well_Name>NewWell_1233</well_Name>

    <well_TD>0.0</well_TD>

    <well_Field>ABCDF</well_Field>

    <well_Operator>Sam</well_Operator>

    <well_Location_LAT>0.0</well_Location_LAT>

    <well_Location_LONG>0.0</well_Location_LONG>

    <well_Country_ID>2080</well_Country_ID>

    <well_Depth_Reference>Rotary table amsl</well_Depth_Reference>

    <well_UWBI>6667990077</well_UWBI>

    <well_Block>block2</well_Block>

    <well_Datum>EQNN</well_Datum>

  </wellHeader>

  .........

  ........

  <wellHeader>

    <well_ID>test123_009</well_ID>

    <well_Name>NewWell_12344</well_Name>

    <well_TD>0.0</well_TD>

    <well_Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true"/>

    <well_Operator xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true"/>

    <well_Location_LAT>0.0</well_Location_LAT>

    <well_Location_LONG>0.0</well_Location_LONG>

    <well_Country_ID>2080</well_Country_ID>

    <well_Depth_Reference xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true"/>

    <well_UWBI xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true"/>

    <well_Block xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true"/>

    <well_Datum xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true"/>

  </wellHeader>

</collection>

and my XQuery is like this:

SELECT

                    *

          FROM

                    (EXEC Source.invoke(binding => 'HTTP', action => 'GET')) AS ws, XMLTABLE('/collection/wellHeader' PASSING ws.result COLUMNS WELL_ID string PATH './well_ID', WELL_Name string PATH './well_Name', WELL_TD float PATH 'wellHeader/well_TD', WELL_Field string PATH './well_Field', WELL_Operator string PATH './well_Operator', WELL_Location_LAT float PATH './well_Location_LAT', WELL_Location_LONG float PATH './well_Location_LONG', WELL_Country_ID integer PATH './well_Country_ID', WELL_Depth_Reference string PATH './well_Depth_Reference', WELL_UWBI string PATH './well_UWBI', WELL_Block string PATH './well_Block', WELL_Datum string PATH './well_Datum') AS GC

When I try to preview data, I get the following warning and nothing is returned:

15:26:07,341 WARN  [PROCESSOR] Processing exception 'Failed to evaluate XQuery expression; Please check the query and correct errors in syntax or usage. ' for request 4Io8R8EVi6qs.0.  Exception type org.teiid.core.TeiidProcessingException thrown from net.sf.saxon.expr.Expression.dynamicError(Expression.java:961). Enable more detailed logging to see the entire stacktrace.

I am using Teiid 7.7 and JBoss AS 5.1.0GA.

I am not find out the problem with the XQuery. Please help me on this.

Thanks,

Tanmoy

[rareddy]

Tanmoy,

Try using the Teiid Designer 7.8, it defines a wizard that can help you create a XMLTable from a web service. Check the imports section for it.

Ramesh..

[rareddy]

IMO the leading "./" on each of the PATH may the issue.

[tanmoypalit]

Thanks Ramesh for the reply!!

I don't think query has any problem. I tried to remove "./" but still it did not work. I think something is wrong with the Teiid designer (I am using with JBoss Developer Studio 5 beta)

But situation is not that bad right now. I created a VDB with the view and now the same query is working fine. I had to deploy the VDB and execute the query from the deployed VDB.

As you suggested, I will also try with Teiid Designer 7.8

- Tanmoy

[shawkins]

Just to follow up, XMLTABLE sees the column paths as relative to the root path, so having './path' is effectively the same as 'path' and as you saw will work either way.  It looks like you may have had a bad vdb initially as I also tried your scenario and extracted data just fine.  If you have an issue like that again it may be good to open a designer or teiid jira with the bad vdb so that we can see what is going wrong.

Steve