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

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

kulbhushanc Dec 20, 2017 5:01 AM

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

shawkins Dec 22, 2017 1:22 PM (in response to kulbhushanc)
> 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.
Actions
2. Re: Model Remote XML File as a Data Source using Teiid Admin API'S

kulbhushanc Jan 24, 2018 8:40 AM (in response to shawkins)

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

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

rareddy Jan 24, 2018 1:15 PM (in response to kulbhushanc)

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

kulbhushanc Jan 25, 2018 1:07 AM (in response to rareddy)

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)
Actions
5. Re: Model Remote XML File as a Data Source using Teiid Admin API'S

shawkins Jan 25, 2018 8:23 AM (in response to kulbhushanc)

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

... PASSING XMLPARSE(f.result WELLFORMED) ...
Actions
6. Re: Model Remote XML File as a Data Source using Teiid Admin API'S

kulbhushanc Jan 29, 2018 8:04 AM (in response to shawkins)

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
Actions
7. Re: Model Remote XML File as a Data Source using Teiid Admin API'S

rareddy Jan 29, 2018 9:59 AM (in response to kulbhushanc)

I think the root of path needs to be like '/teiid:getplantWSResponse/return/CATALOG/PLANT'
Actions
8. Re: Model Remote XML File as a Data Source using Teiid Admin API'S

kulbhushanc Feb 1, 2018 1:45 AM (in response to rareddy)

Again getting empty RESULTSET.
Actions
9. Re: Model Remote XML File as a Data Source using Teiid Admin API'S

rareddy Feb 1, 2018 9:41 AM (in response to kulbhushanc)
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..
Actions

Go to original post