8 Replies Latest reply on Jan 5, 2018 5:08 PM by Steven Hawkins

    Use XML as a datasource in Teiid.

    Kulbhushan Chaskar Master

      Hi,

      I am using teiid 9.1.3 and WildFly 10.0.0,

       

      I am trying to use XML as a relational database using Teiid, I am following steps from blog: https://developer.jboss.org/wiki/ConvertXMLDataIntoRelationalTableDataUsingTeiid , I am able to use XML string as a relational data but to use as file on physical server am facing issues. I am trying to deploy department-ds.xml from Teiid admin console it returned error:

       

      Cannot upload deployment: {"WFLYCTL0080: Failed services" => {"jboss.deployment.unit.\"department-ds.xml\".PARSE" => "org.jboss.msc.service.StartException in service jboss.deployment.unit.\"department-ds.xml\".PARSE: WFLYSRV0153: Failed to process phase PARSE of deployment \"department-ds.xml\" Caused by: org.jboss.as.server.deployment.DeploymentUnitProcessingException: IJ010061: Unexpected element: connection-factories Caused by: org.jboss.jca.common.metadata.ParserException: IJ010061: Unexpected element: connection-factories"}}

       

      Also I have some questions regarding the same.

      1.     Can we use Teiid admin API to create connection to XML file dynamically.

      2.     If yes How it can be done?

       

      Thanks,

      Kulbhushan Chaskar

        • 1. Re: Use XML as a datasource in Teiid.
          Van Halbert Master

          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.
            Ramesh Reddy Master

            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.
              Kulbhushan Chaskar Master

              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.
                Steven Hawkins Master

                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.
                  Kulbhushan Chaskar Master

                  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.
                    Steven Hawkins Master

                    > 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.
                      Kulbhushan Chaskar Master

                      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.
                        Steven Hawkins Master

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