1 2 Previous Next 26 Replies Latest reply on Oct 10, 2014 11:59 AM by Steven Hawkins Go to original post
      • 15. Re: Re: XML data (type as VARCHAR) to XML Table(true XML)
        Steven Hawkins Master

        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.

        • 16. Re: XML data (type as VARCHAR) to XML Table(true XML)
          Lunen De Lange Newbie

          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.

          • 17. Re: XML data (type as VARCHAR) to XML Table(true XML)
            Lunen De Lange Newbie

            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

            • 18. Re: XML data (type as VARCHAR) to XML Table(true XML)
              Steven Hawkins Master

              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.

              • 19. Re: XML data (type as VARCHAR) to XML Table(true XML)
                Lunen De Lange Newbie

                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.

                • 20. Re: Re: XML data (type as VARCHAR) to XML Table(true XML)
                  Steven Hawkins Master

                  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'"

                  • 21. Re: XML data (type as VARCHAR) to XML Table(true XML)
                    Lunen De Lange Newbie

                    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 .

                    • 22. Re: Re: XML data (type as VARCHAR) to XML Table(true XML)
                      Steven Hawkins Master

                      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.

                      • 23. Re: Re: Re: XML data (type as VARCHAR) to XML Table(true XML)
                        Lunen De Lange Newbie

                        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

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

                        • 24. Re: Re: Re: XML data (type as VARCHAR) to XML Table(true XML)
                          Steven Hawkins Master

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

                          • 25. Re: Re: Re: Re: XML data (type as VARCHAR) to XML Table(true XML)
                            Lunen De Lange Newbie

                            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;

                            • 26. Re: Re: Re: Re: XML data (type as VARCHAR) to XML Table(true XML)
                              Steven Hawkins Master

                              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.

                              1 2 Previous Next