[shawkins]

Also fully upgrading to 2.3.6 results in a difference in the module.xml vs. just upgrading the jar files.  The 2.3.6 module.xml version from the resteasy kit exports org.apache.httpcomponents and includes the jandex files.  So this is a little different than the patching that suggest on the download page.

[lunendl]

Hi guys. I managed to get it working last night. I've downloaded the files again and placed it in the folder... something must have been corrupt. Thanks
I've now followed this tutorial (Connect to XML over HTTP using Teiid) to get my answer set into an xmltable.
I'm trying to follow the steps xml source -> xmltable -> relational results

My question is. How do implement this tutorial with a web-service that expects a parameter.

I went back to Convert XML data into Relational Table data using Teiid tutorial again but I'm finding it impossible to interpret and apply to my scenario.
Is there a tutorial or video of how you implement this from the designer itself. Most of the steps in there are about editing files.

[lunendl]

Hi guys. Is what I'm trying to do possible with the product?. To query an external webservice that would need an input parameter and returning it back into a table format.

eg.  http://www.webservicex.net/geoipservice.asmx
Using an IPAddress as parameter. eg. 219.145.93.110
Pseudo code would be something like: Select IP, ReturnCodeDetails, CountryName, CountryCode from GetGeoIP where IPAddress = '219.145.93.110'

Results should then need to be:
IP                               |   ReturnCodeDetails     | CoutryName      | CountryCode
219.145.93.110 | Success        | China     | CHN

Thanks again

[shawkins]

From above assuming that GetGeoIP is a procedure with an input parameter IPAddress, then yes you can use procedural relational syntax to call it as if it were a table/view.

The GetGeoIP procedure would then be defined as creating the request with the given input parameter, calling the appropriate webservice, and passing the resulting xml into xmltable.

[lunendl]

That's great news! Thanks Steven. So the web service(http://www.webservicex.net/geoipservice.asmx) needs to be accessed from the "GetGeoIP procedure"...? Can you PLEASE give me a sample of how the above would be achieved.

[shawkins]

Which part do you need a sample of?  A procedure that you can call as a table:

{code}
create virtual procedure GetGeoIP (in IPAddress string) returns TABLE (IP string, ReturnCodeDetails string, CountryName string, CountryCode string) as
begin
  /*
    create request
    call web service
    create results with xml table
  */
  ...
end
{code}

You could then successfully issue "Select IP, ReturnCodeDetails, CountryName, CountryCode from GetGeoIP where IPAddress = '219.145.93.110'"

[lunendl]

Thanks. Can you please give me a sample of how the virtual procedure should look in terms of "create request/call web service/create results with xml table" would look.

Once I have the virtual procedure, where would I need to go in Teiid to implement/create the procedure for future use? I searched online but the only usefull link I could find was Virtual Procedures - Teiid 8.9 (draft) - Project Documentation Editor which was very basic .

[shawkins]

If you are starting from soap, Teiid Designer will generate the necessary procedures for you - it appears from your earlier posts that you already went through that exercise.

{code}
declare xml request = xmlelement(name request, IPAddress); /* this would recreate <request>IPAddress</request>, alternatively this could be a procedure generated by designer */
declare xml response = (select result from (exec ws_source.invoke(request=>request) ws));
SELECT t.* FROM XMLTABLE(XMLNAMESPACES('http://tempuri.org/' AS tns), '/tns:runQuery1Response' PASSING response COLUMNS runQuery1Result string PATH '/tns:runQuery1Result') AS t
{code}

I just lifted the xmltable from your earlier post.  It would obviously need to match your scenario.  It could also be done as a call out to another procedure with the response xml as the input.

> Once I have the virtual procedure, where would I need to go in Teiid to implement/create the procedure for future use?

If you are using Teiid Designer, then the virtual procedure would be added as a child under a virtual model.  If you are using DDL directly in a Teiid VDB, then you can put it in whatever model/schema you like.

[lunendl]

Morning Steven,
Thanks for the above. 
From your reply: "If you are using Teiid Designer, then the virtual procedure would be added as a child under a virtual model." (Thanks for that, but I didn't explain myself properly - sorry). In my designer, I cant see an option of creating a "virtual procedure" anywhere. I went to "File" - "New" - "Other" and "File" - "Import"

This is what I have for my Virtual Procedure.How does that look?

======================================================================

create virtual procedure GetGeoIP (in IPAddress string) returns TABLE (ReturnCode biginteger, IP string, ReturnCodeDetails string, CountryName string, CountryCode string) as

begin

/*-------this would recreate <request>IPAddress</request>, alternatively this could be a procedure generated by designer */

declare xml request = xmlelement(name request, IPAddress);

/*-------call web service*/

declare xml response = select * from ( exec "GeoIPServiceView"."GetGeoIP"(request) )

/*-------Query produced by end result from Importing - (SOAP)*/

SELECT t.* FROM

XMLTABLE(XMLNAMESPACES('http://www.webservicex.net/' AS tns),

        '/tns:GetGeoIPResponse/tns:GetGeoIPResult'

         PASSING GeoIPServiceView.GetGeoIP_response.xml_in

         COLUMNS ReturnCode biginteger PATH '/tns:ReturnCode',

                 IP string PATH '/tns:IP',

                 ReturnCodeDetails string PATH '/tns:ReturnCodeDetails',

                 CountryName string PATH '/tns:CountryName',

                 CountryCode string PATH '/tns:CountryCode')

AS t;

END

======================================================================

[shawkins]

> In my designer, I cant see an option of creating a "virtual procedure" anywhere. I went to "File" - "New" - "Other" and "File" - "Import"

On a virtual model, right click and choose to add a child procedure.

> This is what I have for my Virtual Procedure.How does that look?

That's the right idea.  However the response statement is not correct - it should be a scalar subquery returning a single column "response = (select result from ..."

Then you just need to create the appropriate request for your scenario and you should be good.

[lunendl]

Thanks Steven. I really appreciate your time with this. (almost there)

I'm getting the following error when I try and preview the data:

select * from ( exec "GeoIPService"."GetGeoIP"('219.145.93.110') ) AS X_X

org.teiid.runtime.client.TeiidClientException: java.lang.RuntimeException: Remote org.teiid.core.TeiidException: java.lang.String cannot be cast to org.teiid.core.types.XMLType

Elapsed Time:  0 hr, 0 min, 0 sec, 133 ms.

Please find attached document of how I've created the Procedure on the interface.

If you can't please let me know.

I have another question. If I'm not sure what columns will be returned by the webservice, would I still be able to use this process and do something like this?:

SELECT t.* FROM

XMLTABLE(XMLNAMESPACES('http://www.webservicex.net/' AS tns),

        '/tns:GetGeoIPResponse/tns:GetGeoIPResult'

         PASSING GeoIPServiceView.GetGeoIP_response.xml_in

         COLUMNS *)

AS t;

[shawkins]

What is GetGeoIP?  Does it take an xml parameter?  If so then we expect it to be a proper xml fragment.

> would I still be able to use this process and do something like this?

If by that you mean pass the xml into xmltables, then yes.  If you mean use an * then no - xmltable needs to know the columns explicitly.