-
1. Re: Use XML as a datasource in Teiid.
van.halbert Dec 19, 2017 8:19 AM (in response to kulbhushanc)Kulbhushan,
I would suggest by looking at the doc's on how to configure the file resource adapter. For examples, here's a link to the file examples in teiid github. I would suggest using the CLI option for configuring the resource-adapter.
HTH
Van
-
2. Re: Use XML as a datasource in Teiid.
rareddy Dec 19, 2017 4:16 PM (in response to kulbhushanc)Resource adapter creation is exactly similar to Mongodb one you asked earlier, change class names and properties accordingly.
Note that with XML model, it also needs a view model that defines the view that makes use of the Xmltable construct.
-
3. Re: Use XML as a datasource in Teiid.
kulbhushanc Dec 21, 2017 4:12 AM (in response to rareddy)Thanks Ramesh & Van,
I am able to create resource adapter for XML file which is located physically on WildFly JBoss server's machine.
but when I am trying to execute query for the XML Teiid Admin API throwing exception, I am following steps from the post Convert XML data into Relational Table data using Teiid and I am trying steps given under the topic Example 2: XML data from a file from the blog. My Select Statement in java is
String sql = "select employee.* from (call department.getTextFiles('*.xml')) f,"+
"xmltable('/dept/employee' PASSING xmlparse(DOCUMENT f.file) COLUMNS"+
"empID integer PATH '@id', "+
"firstname string PATH 'name/first',"+
"lastname string PATH 'name/last') AS employee";
Detailed Stack Trace:
Remote org.teiid.api.exception.query.QueryParserException: TEIID31100 Parsing error: Encountered "DOCUMENT f.file) [*]COLUMNSempID[*] integer PATH" at line 1, column 126.
Was expecting: "and" | "as" | "between" | "in" | "is" | "like" | "like_regex" | "not" | "or" | "over" ...
org.teiid.jdbc.TeiidSQLException: Remote org.teiid.api.exception.query.QueryParserException: TEIID31100 Parsing error: Encountered "DOCUMENT f.file) [*]COLUMNSempID[*] integer PATH" at line 1, column 126.
Was expecting: "and" | "as" | "between" | "in" | "is" | "like" | "like_regex" | "not" | "or" | "over" ...
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:747)
at org.teiid.jdbc.StatementImpl.access$100(StatementImpl.java:65)
at org.teiid.jdbc.StatementImpl$2.onCompletion(StatementImpl.java:561)
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:285)
at org.teiid.net.socket.SocketServerInstanceImpl.read(SocketServerInstanceImpl.java:337)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
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:427)
at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:570)
at org.teiid.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1115)
at org.teiid.jdbc.StatementImpl.executeUpdate(StatementImpl.java:360)
at com.bitwise.mySql.WithTeiid.execute(WithTeiid.java:590)
at com.bitwise.mySql.WithTeiid.executeSelect(WithTeiid.java:209)
at com.bitwise.mySql.WithTeiid.main(WithTeiid.java:48)
Caused by: org.teiid.core.TeiidProcessingException: Remote org.teiid.api.exception.query.QueryParserException: TEIID31100 Parsing error: Encountered "DOCUMENT f.file) [*]COLUMNSempID[*] integer PATH" at line 1, column 126.
Was expecting: "and" | "as" | "between" | "in" | "is" | "like" | "like_regex" | "not" | "or" | "over" ...
at org.teiid.query.parser.QueryParser.convertParserException(QueryParser.java:214)
at org.teiid.query.parser.QueryParser.parseCommand(QueryParser.java:164)
at org.teiid.query.parser.QueryParser.parseCommand(QueryParser.java:140)
at org.teiid.dqp.internal.process.Request.parseCommand(Request.java:306)
at org.teiid.dqp.internal.process.Request.generatePlan(Request.java:407)
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(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
Thanks,
Kulbhushan Chaskar.
-
4. Re: Use XML as a datasource in Teiid.
shawkins Dec 21, 2017 8:19 AM (in response to kulbhushanc)The Java string construction of the query has a problem:
COLUMNS"+
"empID
There is no space between that concatenation.
Steve
-
5. Re: Use XML as a datasource in Teiid.
kulbhushanc Dec 22, 2017 1:33 AM (in response to shawkins)I works!! Thank you very much Steven!!
Can we insert data XML using Teiid? If yes what is the syntax for the same?
-
6. Re: Use XML as a datasource in Teiid.
shawkins Dec 22, 2017 10:57 AM (in response to kulbhushanc)> Can we insert data XML using Teiid? If yes what is the syntax for the same?
It's possible but it would be cumbersome as the whole document would need to be rewritten and you'd need to use entirely different sql/xml syntax to generate the document - see functions like XMLELEMENT, XMLFOREST, etc. and the SAVEFILE procedure on the file translator.
-
7. Re: Use XML as a datasource in Teiid.
kulbhushanc Jan 4, 2018 6:35 AM (in response to shawkins)Thanks Steven..
I am able to execute SELECT query as well as saveFile procedure from fiile translator.
I need know to know are there only one type of syntax available in Teiid? Is there any other syntax or ways to apply these operations?
e.g. instead of using saveFile can we have ANSI-SQL syntax to save the data into file.
-
8. Re: Use XML as a datasource in Teiid.
shawkins Jan 5, 2018 5:08 PM (in response to kulbhushanc)> instead of using saveFile can we have ANSI-SQL syntax to save the data into file.
You can introduce an updatable view with instead triggers to handle the relevant insert, update, delete operations from users, but unfortunately there isn't direct support for an automatic mapping.