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

      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

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

          • 2. Re: Re: XML data (type as VARCHAR) to XML Table(true XML)
            lunendl

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

            • 3. Re: Re: XML data (type as VARCHAR) to XML Table(true XML)
              shawkins

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

              • 4. Re: Re: Re: XML data (type as VARCHAR) to XML Table(true XML)
                lunendl

                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.

                • 5. Re: Re: Re: XML data (type as VARCHAR) to XML Table(true XML)
                  shawkins

                  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.

                  • 6. Re: Re: Re: XML data (type as VARCHAR) to XML Table(true XML)
                    lunendl

                    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.

                    • 7. Re: Re: Re: XML data (type as VARCHAR) to XML Table(true XML)
                      rareddy

                      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.

                      • 8. Re: Re: Re: XML data (type as VARCHAR) to XML Table(true XML)
                        shawkins

                        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.

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

                          • 10. Re: XML data (type as VARCHAR) to XML Table(true XML)
                            shawkins

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

                            • 11. Re: Re: XML data (type as VARCHAR) to XML Table(true XML)
                              lunendl

                              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

                              • 12. Re: Re: XML data (type as VARCHAR) to XML Table(true XML)
                                shawkins

                                Is the rest easy patch installed?

                                • 13. Re: Re: XML data (type as VARCHAR) to XML Table(true XML)
                                  lunendl

                                  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

                                  • 14. Re: Re: XML data (type as VARCHAR) to XML Table(true XML)
                                    rareddy

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

                                    1 2 Previous Next