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

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

    lunendl
    lunendl Oct 7, 2014 7:07 AM

      Hi,

       

      I've created a Webservice that returns Json and XML data as VARCHAR's. Return json content using webservice connection

      I receive the data successfully (with some help from Steve Hawkins), but I somehow need to convert the Json or XML data into a proper XML entity.

      Is there a way to do this? I've tried to use the JSONTOXML function in my original Json response view query from, But I'm getting an error. Please see below

      ORIGINAL:

      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

      TO:

      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.

       

      My main query is:

      select * from ( exec "XML_TEST1_View"."runQueryXML1"('"{"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" ]}"') ) AS X_X

       

      I've also attached a screenshot of the data being returned.

       

      Thanks

        • 1. Re: XML data (type as VARCHAR) to XML Table(true XML)
          shawkins
          shawkins Oct 7, 2014 7:25 AM (in response to lunendl)

          What is the data type of Teiid_FRX_View.runQuery1_response.xml_in?  JSONTOXML expects either clob/string or blob/varbinary.

            • Actions
          • 2. Re: Re: XML data (type as VARCHAR) to XML Table(true XML)
            lunendl
            lunendl Oct 7, 2014 8:16 AM (in response to shawkins)

            Thanks Steven. It's of type string. Please find attached Package Diagram.

              • Actions
            • 3. Re: Re: XML data (type as VARCHAR) to XML Table(true XML)
              shawkins
              shawkins Oct 7, 2014 8:25 AM (in response to lunendl)

              Actually that shows xml_in is of type XMLLiteral (or just XML in Teiid).

                • Actions
              • 4. Re: Re: Re: XML data (type as VARCHAR) to XML Table(true XML)
                lunendl
                lunendl Oct 7, 2014 9:53 AM (in response to shawkins)

                Seven, I'm a bit confused because the Column data type when I do a "Preview Data" states that it's a VARCHAR when I run the test query.Also when I create the relational model from the web service where it states that it's a string.  Find attached screenshots. Sorry about this, I'm really confused at the moment.

                  • Actions
                • 5. Re: Re: Re: XML data (type as VARCHAR) to XML Table(true XML)
                  shawkins
                  shawkins Oct 7, 2014 10:02 AM (in response to lunendl)

                  Lets go back a little bit.  The initial question was around:

                   

                  JSONTOXML('tns:runQuery1Response', Teiid_FRX_View.runQuery1_response.xml_in)

                   

                  From what you are showing me the parameter Teiid_FRX_View.runQuery1_response.xml_in is of type XML - thus you get an error saying that the function is of an unknown form as there's no need to convert from xml to xml.

                    • Actions
                  • 6. Re: Re: Re: XML data (type as VARCHAR) to XML Table(true XML)
                    lunendl
                    lunendl Oct 7, 2014 12:02 PM (in response to shawkins)

                    Steven, yes you are correct. As to what you are saying, the result was always of type xml as far as I understand. My aim for this exercise is to be able to query against the web service after adding the model view to a VDB.

                    Is this a lost cause or should I push through? At this stage, I'm just trying to get at better understanding of what is going on in the environment as I've only been on the platform for just over a week and would like to present a proof of concept to my seniors.

                      • Actions
                    • 7. Re: Re: Re: XML data (type as VARCHAR) to XML Table(true XML)
                      rareddy
                      rareddy Oct 7, 2014 12:16 PM (in response to lunendl)

                      I guess, you need to explain your usecase more has to what you are trying to accomplish? Then may be can guide you through it.

                        • Actions
                      • 8. Re: Re: Re: XML data (type as VARCHAR) to XML Table(true XML)
                        shawkins
                        shawkins Oct 7, 2014 12:42 PM (in response to rareddy)

                        The overview of these scenarios looks something like this:

                         

                        json source -> jsontoxml -> xmltable -> relational results

                        or

                        xml source -> xmltable -> relational results

                         

                        An xml source (or json) can be a file on the filesystem, generated from other teiid logic, or a webservice.  What you have in your designer images shows a flow that looks like:

                         

                        request generation procedure -> web service call

                         

                        So make sure that you understand each of the procedures you have in terms of their purpose, then we can work from there if you have a question about a specific step.

                          • Actions
                        • 9. Re: XML data (type as VARCHAR) to XML Table(true XML)
                          lunendl
                          lunendl Oct 8, 2014 6:21 AM (in response to lunendl)

                          Thanks guys. I really appreciate it. If I understand you correctly. My next move would be to get my xml source, generated from the web service call, into a xmltable.
                          I had a search on the internet and found this old article that you two have produced in 2010/11  https://developer.jboss.org/wiki/ConvertXMLDataIntoRelationalTableDataUsingTeiid

                          Example 3: XML data from a Web Service

                          I tried to follow some of the steps, but product seems to have changed quite a bit between that version of Teiid Designer and 8.7

                          Also, my webservice reside outside of the JBoss/Teiid environment.


                          Thanks again for your patients.

                            • Actions
                          • 10. Re: XML data (type as VARCHAR) to XML Table(true XML)
                            shawkins
                            shawkins Oct 8, 2014 9:15 AM (in response to lunendl)

                            > tried to follow some of the steps, but product seems to have changed quite a bit between that version of Teiid Designer and 8.7

                             

                            Some of the UI may have changed, but the steps are still the same in terms of accessing a source and feeding the resulting xml into xmltable.

                             

                            For more Designer centric does, look at the newer Teiid Designer 7.7 What's New and Examples - Teiid Examples - Project Documentation Editor

                             

                            > Also, my webservice reside outside of the JBoss/Teiid environment.

                             

                            The article is only an example and is trying to be self contained.  It is typically the case that the webservice will be elsewhere.

                              • Actions
                            • 11. Re: Re: XML data (type as VARCHAR) to XML Table(true XML)
                              lunendl
                              lunendl Oct 8, 2014 10:16 AM (in response to shawkins)

                              Thanks Steven. I've followed the steps described in Connect to XML over HTTP using Teiid

                              I assume this is the step I need to take to create an XMLTable.
                              Please find attached steps that I've followed. However, I'm getting an error on the "Preview Data.

                               

                              select * from "PlantCatalog"."PlantCatalog_table"


                              org.teiid.runtime.client.TeiidClientException: java.lang.RuntimeException: Remote org.teiid.core.TeiidException: java.lang.ClassNotFoundException: com.sun.ws.rs.ext.RuntimeDelegateImpl from [Module "javax.ws.rs.api:main" from local module loader @326d888e (finder: local module finder @35203c7f (roots: /usr/JBOSS_TEIID/jboss-eap-6.1/modules,/usr/JBOSS_TEIID/jboss-eap-6.1/modules/system/layers/base))]

                              .

                              ====================================Console feed==============================================

                               

                               

                              15:05:36,692 SEVERE [org.apache.cxf.jaxrs.client.JAXRSClientFactoryBean] (Worker29_QueryProcessorQueue50) xoMnBSkLrP4L java.lang.RuntimeException : java.lang.ClassNotFoundException: com.sun.ws.rs.ext.

                              RuntimeDelegateImpl from [Module "javax.ws.rs.api:main" from local module loader @326d888e (finder: local module finder @35203c7f (roots: /fraXses/JBOSS_TEIID/jboss-eap-6.1/modules,/fraXses/JBOSS_TEII

                              D/jboss-eap-6.1/modules/system/layers/base))]

                              15:05:36,692 ERROR [org.teiid.CONNECTOR] (Worker29_QueryProcessorQueue50) xoMnBSkLrP4L Connector worker process failed for atomic-request=xoMnBSkLrP4L.0.3.13: java.lang.RuntimeException: java.lang.Cla

                              ssNotFoundException: com.sun.ws.rs.ext.RuntimeDelegateImpl from [Module "javax.ws.rs.api:main" from local module loader @326d888e (finder: local module finder @35203c7f (roots: /fraXses/JBOSS_TEIID/jb

                              oss-eap-6.1/modules,/fraXses/JBOSS_TEIID/jboss-eap-6.1/modules/system/layers/base))]

                                at javax.ws.rs.ext.RuntimeDelegate.findDelegate(RuntimeDelegate.java:134)

                                at javax.ws.rs.ext.RuntimeDelegate.getInstance(RuntimeDelegate.java:96)

                                at javax.ws.rs.core.UriBuilder.newInstance(UriBuilder.java:74)

                                at javax.ws.rs.core.UriBuilder.fromUri(UriBuilder.java:87)

                                at org.apache.cxf.jaxrs.client.LocalClientState.<init>(LocalClientState.java:50)

                                at org.apache.cxf.jaxrs.client.AbstractClient.<init>(AbstractClient.java:100)

                                at org.apache.cxf.jaxrs.client.WebClient.<init>(WebClient.java:78)

                                at org.apache.cxf.jaxrs.client.WebClient.<init>(WebClient.java:74)

                                at org.apache.cxf.jaxrs.client.JAXRSClientFactoryBean.createWebClient(JAXRSClientFactoryBean.java:208)

                                at org.apache.cxf.jaxrs.client.WebClient.create(WebClient.java:92)

                                at org.teiid.resource.adapter.ws.WSConnectionImpl$HttpDispatch.<init>(WSConnectionImpl.java:125)

                                at org.teiid.resource.adapter.ws.WSConnectionImpl.createDispatch(WSConnectionImpl.java:287)

                                at org.teiid.translator.ws.BinaryWSProcedureExecution.execute(BinaryWSProcedureExecution.java:119)

                                at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:325) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

                                at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:298) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

                                at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:110) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

                                at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:107) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

                                at java.util.concurrent.FutureTask.run(FutureTask.java:262) [rt.jar:1.7.0_67]

                                at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:58) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

                                at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:274) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

                                at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

                                at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:214) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

                                at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_67]

                                at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_67]

                                at java.lang.Thread.run(Thread.java:745) [rt.jar:1.7.0_67]

                              Caused by: java.lang.ClassNotFoundException: com.sun.ws.rs.ext.RuntimeDelegateImpl from [Module "javax.ws.rs.api:main" from local module loader @326d888e (finder: local module finder @35203c7f (roots:

                              /fraXses/JBOSS_TEIID/jboss-eap-6.1/modules,/fraXses/JBOSS_TEIID/jboss-eap-6.1/modules/system/layers/base))]

                                at org.jboss.modules.ModuleClassLoader.findClass(ModuleClassLoader.java:190) [jboss-modules.jar:1.2.0.CR1]

                                at org.jboss.modules.ConcurrentClassLoader.performLoadClassUnchecked(ConcurrentClassLoader.java:468) [jboss-modules.jar:1.2.0.CR1]

                                at org.jboss.modules.ConcurrentClassLoader.performLoadClassChecked(ConcurrentClassLoader.java:456) [jboss-modules.jar:1.2.0.CR1]

                                at org.jboss.modules.ConcurrentClassLoader.performLoadClass(ConcurrentClassLoader.java:398) [jboss-modules.jar:1.2.0.CR1]

                                at org.jboss.modules.ConcurrentClassLoader.loadClass(ConcurrentClassLoader.java:120) [jboss-modules.jar:1.2.0.CR1]

                                at java.lang.Class.forName0(Native Method) [rt.jar:1.7.0_67]

                                at java.lang.Class.forName(Class.java:190) [rt.jar:1.7.0_67]

                                at javax.ws.rs.ext.FactoryFinder.newInstance(FactoryFinder.java:77)

                                at javax.ws.rs.ext.FactoryFinder.find(FactoryFinder.java:201)

                                at javax.ws.rs.ext.RuntimeDelegate.findDelegate(RuntimeDelegate.java:113)

                                ... 24 more

                               

                               

                              15:05:36,696 ERROR [org.teiid.PROCESSOR] (Worker28_QueryProcessorQueue51) xoMnBSkLrP4L TEIID30019 Unexpected exception for request xoMnBSkLrP4L.0: java.lang.RuntimeException: java.lang.ClassNotFoundEx

                              ception: com.sun.ws.rs.ext.RuntimeDelegateImpl from [Module "javax.ws.rs.api:main" from local module loader @326d888e (finder: local module finder @35203c7f (roots: /fraXses/JBOSS_TEIID/jboss-eap-6.1/

                              modules,/fraXses/JBOSS_TEIID/jboss-eap-6.1/modules/system/layers/base))]

                                at javax.ws.rs.ext.RuntimeDelegate.findDelegate(RuntimeDelegate.java:134)

                                at javax.ws.rs.ext.RuntimeDelegate.getInstance(RuntimeDelegate.java:96)

                                at javax.ws.rs.core.UriBuilder.newInstance(UriBuilder.java:74)

                                at javax.ws.rs.core.UriBuilder.fromUri(UriBuilder.java:87)

                                at org.apache.cxf.jaxrs.client.LocalClientState.<init>(LocalClientState.java:50)

                                at org.apache.cxf.jaxrs.client.AbstractClient.<init>(AbstractClient.java:100)

                                at org.apache.cxf.jaxrs.client.WebClient.<init>(WebClient.java:78)

                                at org.apache.cxf.jaxrs.client.WebClient.<init>(WebClient.java:74)

                                at org.apache.cxf.jaxrs.client.JAXRSClientFactoryBean.createWebClient(JAXRSClientFactoryBean.java:208)

                                at org.apache.cxf.jaxrs.client.WebClient.create(WebClient.java:92)

                                at org.teiid.resource.adapter.ws.WSConnectionImpl$HttpDispatch.<init>(WSConnectionImpl.java:125)

                                at org.teiid.resource.adapter.ws.WSConnectionImpl.createDispatch(WSConnectionImpl.java:287)

                                at org.teiid.translator.ws.BinaryWSProcedureExecution.execute(BinaryWSProcedureExecution.java:119)

                                at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:325) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

                                at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:298) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

                                at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:110) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

                                at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:107) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

                                at java.util.concurrent.FutureTask.run(FutureTask.java:262) [rt.jar:1.7.0_67]

                                at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:58) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

                                at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:274) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

                                at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

                                at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:214) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

                                at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_67]

                                at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_67]

                                at java.lang.Thread.run(Thread.java:745) [rt.jar:1.7.0_67]

                              Caused by: java.lang.ClassNotFoundException: com.sun.ws.rs.ext.RuntimeDelegateImpl from [Module "javax.ws.rs.api:main" from local module loader @326d888e (finder: local module finder @35203c7f (roots:

                              /fraXses/JBOSS_TEIID/jboss-eap-6.1/modules,/fraXses/JBOSS_TEIID/jboss-eap-6.1/modules/system/layers/base))]

                                at org.jboss.modules.ModuleClassLoader.findClass(ModuleClassLoader.java:190) [jboss-modules.jar:1.2.0.CR1]

                                at org.jboss.modules.ConcurrentClassLoader.performLoadClassUnchecked(ConcurrentClassLoader.java:468) [jboss-modules.jar:1.2.0.CR1]

                                at org.jboss.modules.ConcurrentClassLoader.performLoadClassChecked(ConcurrentClassLoader.java:456) [jboss-modules.jar:1.2.0.CR1]

                                at org.jboss.modules.ConcurrentClassLoader.performLoadClass(ConcurrentClassLoader.java:398) [jboss-modules.jar:1.2.0.CR1]

                                at org.jboss.modules.ConcurrentClassLoader.loadClass(ConcurrentClassLoader.java:120) [jboss-modules.jar:1.2.0.CR1]

                                at java.lang.Class.forName0(Native Method) [rt.jar:1.7.0_67]

                                at java.lang.Class.forName(Class.java:190) [rt.jar:1.7.0_67]

                                at javax.ws.rs.ext.FactoryFinder.newInstance(FactoryFinder.java:77)

                                at javax.ws.rs.ext.FactoryFinder.find(FactoryFinder.java:201)

                                at javax.ws.rs.ext.RuntimeDelegate.findDelegate(RuntimeDelegate.java:113)

                                ... 24 more

                                • Actions
                              • 12. Re: Re: XML data (type as VARCHAR) to XML Table(true XML)
                                shawkins
                                shawkins Oct 8, 2014 10:19 AM (in response to lunendl)

                                Is the rest easy patch installed?

                                  • Actions
                                • 13. Re: Re: XML data (type as VARCHAR) to XML Table(true XML)
                                  lunendl
                                  lunendl Oct 8, 2014 11:36 AM (in response to shawkins)

                                  I believe I have. I've replaced the physical files in /usr/JBOSS_TEIID/jboss-eap-6.1/modules/system/layers/base/org/jboss/resteasy/resteasy-jaxrs/main/

                                  with the latest resteasy files:
                                  module.xml
                                  async-http-servlet-3.0-2.3.6.Final.jar
                                  async-http-servlet-3.0-2.3.6.Final-jandex.jar

                                  resteasy-jaxrs-2.3.6.Final.jar

                                  resteasy-jaxrs-2.3.6.Final-jandex.jar

                                   

                                  Version information:
                                  JBoss Alpha 6.1
                                  Teiid Designer 8.6

                                   

                                   

                                  I also tried replacing the entire resteasy folder(/usr/JBOSS_TEIID/jboss-eap-6.1/modules/system/layers/base/org/jboss/resteasy) with the later(2.3.6) version. The previous version was 2.3.5.


                                  I'm still getting the same error message

                                    • Actions
                                  • 14. Re: Re: XML data (type as VARCHAR) to XML Table(true XML)
                                    rareddy
                                    rareddy Oct 8, 2014 12:07 PM (in response to lunendl)

                                    Can you see following this article EJB as Resteasy client -> ClassNotFoundException changing the <jboss-as>/modules/system/layers/base/org/jboss/resteasy/resteasy-jaxrs/main/module.xml file change

                                     

                                    <module name="javax.ws.rs.api"/>

                                    to

                                    <module name="javax.ws.rs.api" services="export" export="true" />

                                     

                                    After that restart your server. We have not seen this before.

                                     

                                    Ramesh..

                                      • Actions
                                    1 2 Previous Next