8 Replies Latest reply on Oct 15, 2012 3:51 PM by Ramesh Reddy

    Does Teiid Support Native XML Databases ?

    Samaresh Sridhar Newbie

      Hi.

      I have a few xml files that I need to work with along with a relational database.

      Does Teiid support native xml databases or do I have to convert those xml files to some other format like relational tables or csv ?

        • 1. Re: Does Teiid Support Native XML Databases ?
          Ramesh Reddy Master

          Samaresh,

           

          Teiid supports various SQXML language objects and functions. There are lots of XMLTABLE examples on web. Teiid has this example https://community.jboss.org/wiki/ConvertXMLDataIntoRelationalTableDataUsingTeiid

          The example shows reading the XML from file or web service. Teiid supports any type of data, for some we have translators for others Teiid provides API for you to extend. Yes, the idea is convert the XML data into tabular and then use that data to further integrate data from the relational or other sources and then provide the abstraction layer (canonical views) on top.

           

          Ramesh..

          • 3. Re: Does Teiid Support Native XML Databases ?
            Samaresh Sridhar Newbie

            I followed the post that you posted.

            After creating a view model from the teiid designer, I type in a query in the transformation bar

            SELECT Multidb.* FROM

            (CALL M05.getTextFiles('M05-00000279.xml'))  f,  XMLTABLE( XMLNAMESPACES('http://schemas.microsoft.com/office/infopath/2003/myXSD/2005-11-03T09-00-35' as my), '/my:myField/my:MetaInformation' PASSING XMLPARSE(DOCUMENT f.file) COLUMNS ID string PATH '/my:Id', Version integer PATH /'my:Version') AS Multidb

             

            I get an error :

            he query is not parsable.

            Parsing error: Invalid alias format: [M05.getTextFiles]

             

            what should I put before .getTextFiles() ?

            • 4. Re: Does Teiid Support Native XML Databases ?
              Ramesh Reddy Master

              Samaresh,

               

              Make sure you have defined a "file" based model by name "M05"  and your XML file is accessible.  In the Teiid Designer, you can find a wizard in "imports" section that can help you create the above query and model. The example I have provided link was developed before the wizard in Teiid Designer as been developed. Using this you can visually create the table from XML data.

               

              Ramesh..

              • 5. Re: Does Teiid Support Native XML Databases ?
                Samaresh Sridhar Newbie

                I am using Teiid Designer 7.7

                So when I go to import>Metadata Modeling>

                I can see Metadata from Text Files on file system.

                Is that the model that I need to select ?

                • 6. Re: Does Teiid Support Native XML Databases ?
                  Ramesh Reddy Master

                  Import -> Teiid Designer -> File Source (XML) >> Source and View Model

                  • 7. Re: Does Teiid Support Native XML Databases ?
                    Samaresh Sridhar Newbie

                    My xml file has a namespace prefix "my"

                     

                    when I type the query :

                     

                    SELECT Multidb.* FROM

                    (CALL M05.getTextFiles('M05-00000279.xml'))  f,  XMLTABLE( XMLNAMESPACES('http://schemas.microsoft.com/office/infopath/2003/myXSD/2005-11-03T09-00-35' as "my"), '$d/my:myFields/my:MetaInformation' PASSING XMLPARSE(DOCUMENT f.file)AS d COLUMNS ID string PATH 'my:Id', Version integer PATH 'my:Version') AS Multidb

                     

                    I get an error

                    org.teiid.jdbc.TeiidSQLException: Remote org.teiid.api.exception.query.QueryParserException: Parsing error: Encountered "my" at line 1, column 346.

                    Was expecting:

                        <STRINGVAL> ...

                     

                     

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

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

                            at org.teiid.jdbc.StatementImpl.postReceiveResults(StatementImpl.java:62

                    4)

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

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

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

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

                     

                     

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

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

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

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

                    voke(SocketServerConnectionFactory.java:110)

                            at $Proxy1.read(Unknown Source)

                            at org.teiid.net.socket.SocketServerInstanceImpl$RemoteInvocationHandler

                    $1.get(SocketServerInstanceImpl.java:349)

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

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

                            at JDBCClient.execute(JDBCClient.java:74)

                            at JDBCClient.main(JDBCClient.java:47)

                    Caused by: [TeiidProcessingException]Remote org.teiid.api.exception.query.QueryP

                    arserException: Parsing error: Encountered "my" at line 1, column 346.

                    Was expecting:

                        <STRINGVAL> ...

                     

                     

                            at org.teiid.query.parser.QueryParser.convertParserException(QueryParser.java:194)

                            at org.teiid.query.parser.QueryParser.parseCommand(QueryParser.java:148)

                     

                     

                            at org.teiid.query.parser.QueryParser.parseCommand(QueryParser.java:124)

                     

                     

                            at org.teiid.dqp.internal.process.Request.parseCommand(Request.java:309)

                     

                     

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

                     

                     

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

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

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

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

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

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

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

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

                            at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)

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

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