XML data (type as VARCHAR) to XML Table(true XML)| JBoss.org Content Archive (Read Only)

1 2 Previous Next 26 Replies Latest reply on Oct 10, 2014 11:59 AM by shawkins Go to original post

15. Re: Re: XML data (type as VARCHAR) to XML Table(true XML)

shawkins Oct 8, 2014 12:14 PM (in response to rareddy)

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.
Actions
16. Re: XML data (type as VARCHAR) to XML Table(true XML)

lunendl Oct 9, 2014 4:22 AM (in response to shawkins)

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.
Actions
17. Re: XML data (type as VARCHAR) to XML Table(true XML)

lunendl Oct 9, 2014 9:43 AM (in response to 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
Actions
18. Re: XML data (type as VARCHAR) to XML Table(true XML)

shawkins Oct 9, 2014 9:46 AM (in response to lunendl)

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.
Actions
19. Re: XML data (type as VARCHAR) to XML Table(true XML)

lunendl Oct 9, 2014 11:11 AM (in response to shawkins)

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.
Actions
20. Re: Re: XML data (type as VARCHAR) to XML Table(true XML)

shawkins Oct 9, 2014 11:15 AM (in response to lunendl)
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'"
Actions
21. Re: XML data (type as VARCHAR) to XML Table(true XML)

lunendl Oct 9, 2014 12:11 PM (in response to shawkins)

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 .
Actions
22. Re: Re: XML data (type as VARCHAR) to XML Table(true XML)

shawkins Oct 9, 2014 1:48 PM (in response to lunendl)
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.
Actions
23. Re: Re: Re: XML data (type as VARCHAR) to XML Table(true XML)

lunendl Oct 10, 2014 6:07 AM (in response to shawkins)

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
======================================================================
Actions
24. Re: Re: Re: XML data (type as VARCHAR) to XML Table(true XML)

shawkins Oct 10, 2014 8:10 AM (in response to lunendl)

> 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.
Actions
25. Re: Re: Re: Re: XML data (type as VARCHAR) to XML Table(true XML)

lunendl Oct 10, 2014 11:14 AM (in response to shawkins)
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;

WebserviceProcedureSetup.doc 317.5 KB
Actions
26. Re: Re: Re: Re: XML data (type as VARCHAR) to XML Table(true XML)

shawkins Oct 10, 2014 11:59 AM (in response to lunendl)

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.
Actions

1 2 Previous Next

Go to original post