9 Replies Latest reply on Feb 1, 2018 9:41 AM by Ramesh Reddy

    Model Remote XML File as a Data Source using Teiid Admin API'S

    Kulbhushan Chaskar Master

      Hi,

       

      I am using Teiid Admin API 9.1.3 and WildFly 10.0.0. I need to Remote XML File as a datasource, While searching on internet I can across post: Model Remote XML File Data Source - Teiid Examples - Project Documentation Editor  , Its for using Teiid Designer, Could you please suggest me the similar steps using Teiid Admin API's.

      So here I am mainly looking for:

      1.     How to make connection with remote XML file?

      2.     Do we need to provide schema definition in VDB file or Teiid by default read XML file and provide schema structure?

       

      Thanks,

      Kulbhushan Chaskar

        • 1. Re: Model Remote XML File as a Data Source using Teiid Admin API'S
          Steven Hawkins Master

          > How to make connection with remote XML file?

           

          You need to use the Webservices resource adapter.  Adding through the admin api could look like:

           

          Properties p = new Properties();
          p.setProperty("class-name", "org.teiid.resource.adapter.ws.WSManagedConnectionFactory");
          p.setProperty("EndPoint", "http://...");
          admin.createDataSource("myWebse:rviceDS", "webservice", p);

           

          > Do we need to provide schema definition in VDB file or Teiid by default read XML file and provide schema structure?

           

          You always need a VDB.  You'll use the Webservices or WS translator with the resource adapter.  How you expose the XML from there is up to your needs.  If you want it as tabular data then you'll want a view or procedure that reads processes the xml - probably with the XMLTABLE table function.

          • 2. Re: Model Remote XML File as a Data Source using Teiid Admin API'S
            Kulbhushan Chaskar Master

            Thanks you very much Steven...

            I am trying to execute SELECT query on remote XML file from location https://www.w3schools.com/xml/plant_catalog.xml , but is throwing an exception.

             

            Following is the information about the same:

            Java code:

            String select_par_sql = "SELECT PLANT.* from (call plantWS.invoke(request=>'<teiid:getplantWS xmlns:teiid=\"http://teiid.org\"/>',binding='HTTP')) f,"+  

            "xmltable(XMLNAMESPACES('http://teiid.org' as \"teiid\"), "+  

                "  '/teiid:getplantWSResponse/return/PLANT' PASSING f.result COLUMNS "+  

                "  COMMON string     PATH 'COMMON', "+ 

                "  BOTANICAL  string     PATH 'BOTANICAL') AS PLANT";

             

            Query generated from java code:

            SELECT PLANT.* from (call plantWS.invoke(request=>'<teiid:getplantWS xmlns:teiid="http://teiid.org"/>',binding='HTTP')) f,xmltable(XMLNAMESPACES('http://teiid.org' as "teiid"),   '/teiid:getplantWSResponse/return/PLANT' PASSING f.result COLUMNS   COMMON string     PATH 'COMMON',   BOTANICAL  string     PATH 'BOTANICAL') AS PLANT

             

            Resource adapter from standalone-teiid.xml:

            <resource-adapter id="wsxml1">

                                <module slot="main" id="org.jboss.teiid.resource-adapter.webservice"/>

                                <transaction-support>NoTransaction</transaction-support>

                                <connection-definitions>

                                    <connection-definition class-name="org.teiid.resource.adapter.ws.WSManagedConnectionFactory" jndi-name="java:/wsxml1" enabled="true" pool-name="wsxml1">

                                        <config-property name="EndPoint">

                                            https://www.w3schools.com/xml/plant_catalog.xml

                                        </config-property>

                                    </connection-definition>

                                </connection-definitions>

                            </resource-adapter>

             

            VDB file content:

            <?xml version="1.0" encoding="UTF-8" standalone="yes"?> 

            <vdb name="treeWS" version="1"> 

                <model name="plantWS"> 

                    <source name="web-connector" translator-name="ws" connection-jndi-name="wsxml1"/> 

                </model> 

            </vdb>

             

             

            Exception Stack trace:

            TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 web-connector: An endpoint must be specified by the procedure call or have a default value set by the EndPoint datasource property.org.teiid.jdbc.TeiidSQLException: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 web-connector: An endpoint must be specified by the procedure call or have a default value set by the EndPoint datasource property.

            at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:135)

            at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:71)

            at org.teiid.jdbc.StatementImpl.postReceiveResults(StatementImpl.java:703)

            at org.teiid.jdbc.StatementImpl.access$100(StatementImpl.java:64)

            at org.teiid.jdbc.StatementImpl$2.onCompletion(StatementImpl.java:542)

            at org.teiid.client.util.ResultsFuture.done(ResultsFuture.java:135)

            at org.teiid.client.util.ResultsFuture.access$200(ResultsFuture.java:40)

            at org.teiid.client.util.ResultsFuture$1.receiveResults(ResultsFuture.java:79)

            at org.teiid.net.socket.SocketServerInstanceImpl.receivedMessage(SocketServerInstanceImpl.java:268)

            at org.teiid.net.socket.SocketServerInstanceImpl.read(SocketServerInstanceImpl.java:306)

            at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

            at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

            at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

            at java.lang.reflect.Method.invoke(Method.java:497)

            at org.teiid.net.socket.SocketServerConnectionFactory$ShutdownHandler.invoke(SocketServerConnectionFactory.java:98)

            at com.sun.proxy.$Proxy1.read(Unknown Source)

            at org.teiid.net.socket.SocketServerInstanceImpl$RemoteInvocationHandler$1.get(SocketServerInstanceImpl.java:405)

            at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:551)

            at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:404)

            at org.teiid.jdbc.StatementImpl.executeQuery(StatementImpl.java:338)

            at com.bitwise.mySql.WithTeiid.execute(WithTeiid.java:712)

            at com.bitwise.mySql.WithTeiid.executeSelect(WithTeiid.java:272)

            at com.bitwise.mySql.WithTeiid.main(WithTeiid.java:55)

            Caused by: org.teiid.core.TeiidProcessingException: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 web-connector: An endpoint must be specified by the procedure call or have a default value set by the EndPoint datasource property.

            at org.teiid.dqp.internal.process.DataTierTupleSource.exceptionOccurred(DataTierTupleSource.java:401)

            at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:161)

            at org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:392)

            at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:282)

            at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:150)

            at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:282)

            at org.teiid.query.processor.BatchIterator.finalRow(BatchIterator.java:69)

            at org.teiid.common.buffer.AbstractTupleSource.getCurrentTuple(AbstractTupleSource.java:70)

            at org.teiid.query.processor.BatchIterator.getCurrentTuple(BatchIterator.java:84)

            at org.teiid.common.buffer.AbstractTupleSource.hasNext(AbstractTupleSource.java:92)

            at org.teiid.query.processor.relational.NestedTableJoinStrategy.process(NestedTableJoinStrategy.java:101)

            at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:227)

            at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:282)

            at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:150)

            at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:282)

            at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:145)

            at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:151)

            at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:114)

            at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:164)

            at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:146)

            at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:477)

            at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:349)

            at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51)

            at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:275)

             

             

            at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:282)

            at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)

            at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)

            at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)

            at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)

            at java.lang.Thread.run(Thread.java:745)

            Caused by: org.teiid.core.TeiidException: Remote org.teiid.translator.TranslatorException: An endpoint must be specified by the procedure call or have a default value set by the EndPoint datasource property.

            at org.teiid.translator.ws.WSProcedureExecution.execute(WSProcedureExecution.java:140)

            at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:367)

            at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

            at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

            at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

            at java.lang.reflect.Method.invoke(Method.java:497)

            at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:220)

            at com.sun.proxy.$Proxy27.execute(Unknown Source)

            at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306)

            at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112)

            at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)

            at java.util.concurrent.FutureTask.run(FutureTask.java:266)

            at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)

            ... 6 more

            Caused by: javax.xml.ws.WebServiceException: Remote javax.xml.ws.WebServiceException: An endpoint must be specified by the procedure call or have a default value set by the EndPoint datasource property.

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

            at org.teiid.translator.ws.WSProcedureExecution.execute(WSProcedureExecution.java:103)

            ... 18 more

             

            Thanks,

            Kulbhushan Chaskar.

            • 3. Re: Model Remote XML File as a Data Source using Teiid Admin API'S
              Ramesh Reddy Master

              try

               

              String select_par_sql = "SELECT PLANT.* from (call plantWS.invokeHttp(action=>'GET')) f,"+  
              "xmltable(XMLNAMESPACES('http://teiid.org' as \"teiid\"), "+  
                  "  '/teiid:getplantWSResponse/return/PLANT' PASSING f.result COLUMNS "+ 
                  "  COMMON string    PATH 'COMMON', "+
                  "  BOTANICAL  string    PATH 'BOTANICAL') AS PLANT";
              • 4. Re: Model Remote XML File as a Data Source using Teiid Admin API'S
                Kulbhushan Chaskar Master

                Above query is not working for me, looks like there is syntactical error, its giving another exception.

                 

                Query generated by java code:

                SELECT PLANT.* from (call plantWS.invokeHttp(action=>'GET')) f,XMLTABLE(XMLNAMESPACES('http://teiid.org' as "teiid"),   '/teiid:getplantWSResponse/return/PLANT' PASSING f.result COLUMNS   COMMON string    PATH 'COMMON',   BOTANICAL  string    PATH 'BOTANICAL') AS PLANT

                Exception:

                TEIID30492 Remote org.teiid.api.exception.query.QueryValidatorException: TEIID30492 XMLTABLE, XMLEXISTS, or XMLQUERY PASSING context item must be an XML value.org.teiid.jdbc.TeiidSQLException: TEIID30492 Remote org.teiid.api.exception.query.QueryValidatorException: TEIID30492 XMLTABLE, XMLEXISTS, or XMLQUERY PASSING context item must be an XML value.

                at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:135)

                at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:71)

                at org.teiid.jdbc.StatementImpl.postReceiveResults(StatementImpl.java:703)

                at org.teiid.jdbc.StatementImpl.access$100(StatementImpl.java:64)

                at org.teiid.jdbc.StatementImpl$2.onCompletion(StatementImpl.java:542)

                at org.teiid.client.util.ResultsFuture.done(ResultsFuture.java:135)

                at org.teiid.client.util.ResultsFuture.access$200(ResultsFuture.java:40)

                at org.teiid.client.util.ResultsFuture$1.receiveResults(ResultsFuture.java:79)

                at org.teiid.net.socket.SocketServerInstanceImpl.receivedMessage(SocketServerInstanceImpl.java:268)

                at org.teiid.net.socket.SocketServerInstanceImpl.read(SocketServerInstanceImpl.java:306)

                at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

                at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

                at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

                at java.lang.reflect.Method.invoke(Method.java:497)

                at org.teiid.net.socket.SocketServerConnectionFactory$ShutdownHandler.invoke(SocketServerConnectionFactory.java:98)

                at com.sun.proxy.$Proxy1.read(Unknown Source)

                at org.teiid.net.socket.SocketServerInstanceImpl$RemoteInvocationHandler$1.get(SocketServerInstanceImpl.java:405)

                at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:551)

                at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:404)

                at org.teiid.jdbc.StatementImpl.executeQuery(StatementImpl.java:338)

                at com.bitwise.mySql.WithTeiid.execute(WithTeiid.java:717)

                at com.bitwise.mySql.WithTeiid.executeSelect(WithTeiid.java:277)

                at com.bitwise.mySql.WithTeiid.main(WithTeiid.java:55)

                Caused by: org.teiid.core.TeiidProcessingException: TEIID30492 Remote org.teiid.api.exception.query.QueryValidatorException: TEIID30492 XMLTABLE, XMLEXISTS, or XMLQUERY PASSING context item must be an XML value.

                at org.teiid.dqp.internal.process.Request.validateWithVisitor(Request.java:342)

                at org.teiid.dqp.internal.process.Request.validateQuery(Request.java:291)

                at org.teiid.dqp.internal.process.Request.generatePlan(Request.java:429)

                at org.teiid.dqp.internal.process.Request.processRequest(Request.java:481)

                at org.teiid.dqp.internal.process.RequestWorkItem.processNew(RequestWorkItem.java:657)

                at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:338)

                at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51)

                at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:275)

                at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:282)

                at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)

                at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)

                at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)

                at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)

                at java.lang.Thread.run(Thread.java:745)

                • 5. Re: Model Remote XML File as a Data Source using Teiid Admin API'S
                  Steven Hawkins Master

                  There isn't an implicit conversion applicable here from your binary result, so you need to use xmlparse:

                   

                  ... PASSING XMLPARSE(f.result WELLFORMED) ...

                  • 6. Re: Model Remote XML File as a Data Source using Teiid Admin API'S
                    Kulbhushan Chaskar Master

                    As you suggested...

                    For Query :

                    String select_par_sql = "SELECT PLANT.* from (call plantWS.invokeHttp(action=>'GET')) f,"+   

                    "XMLTABLE(XMLNAMESPACES('http://teiid.org' as \"teiid\"), "+   

                        "  '/teiid:getplantWSResponse/return/PLANT' PASSING XMLPARSE(f.result WELLFORMED) COLUMNS "+  

                        "  COMMON string    PATH 'COMMON', "+ 

                        "  BOTANICAL  string    PATH 'BOTANICAL') AS PLANT";

                    Its throwing below exception:

                    Remote org.teiid.api.exception.query.QueryParserException: TEIID31100 Parsing error: Encountered "PASSING XMLPARSE([*]f.result[*] WELLFORMED)" at line 1, column 179.

                    Was expecting: "document" | "content"

                     

                    When I tried passing DOCUMENT in XMLPARSE function i.e.

                     

                    String select_par_sql = "SELECT PLANT.* from (call plantWS.invokeHttp(action=>'GET')) f,"+   

                    "XMLTABLE(XMLNAMESPACES('http://teiid.org' as \"teiid\"), "+   

                        "  '/teiid:getplantWSResponse/return/PLANT' PASSING XMLPARSE(DOCUMENT f.result WELLFORMED) COLUMNS "+  

                        "  COMMON string    PATH 'COMMON', "+ 

                        "  BOTANICAL  string    PATH 'BOTANICAL') AS PLANT";

                     

                    it's not giving any exception but I got blank RESULTSET i.e. result set with no records.

                     

                    Thanks,

                    Kulbhushan Chaskar

                    • 7. Re: Model Remote XML File as a Data Source using Teiid Admin API'S
                      Ramesh Reddy Master

                      I think the root of path needs to be like '/teiid:getplantWSResponse/return/CATALOG/PLANT'

                      • 9. Re: Model Remote XML File as a Data Source using Teiid Admin API'S
                        Ramesh Reddy Master

                        Use

                         

                        SELECT PLANT.* from (call plantWs.invokeHttp(action=>'GET')) f, 
                        XMLTABLE('/CATALOG/PLANT' PASSING XMLPARSE(DOCUMENT f.result WELLFORMED) COLUMNS 
                            COMMON string PATH 'COMMON',
                            BOTANICAL string  PATH 'BOTANICAL') AS PLANT

                         

                        and I verified that it returns the results. When in question you need to see what the XML document that is returning and design your root path according to that on XMLTABLE. You do not need to declare a namespace either. Only when the document you are trying to read has a namespace, then you would need to define one to match them correctly.

                         

                        Ramesh..