Does Teiid Support Native XML Databases ?

[ssamaresh]

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 ?

[rareddy]

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

[ssamaresh]

Thanks.

[ssamaresh]

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() ?

[rareddy]

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

[ssamaresh]

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 ?

[rareddy]

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

[ssamaresh]

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)

[rareddy]

see this

https://community.jboss.org/message/751192#751192

https://community.jboss.org/message/589864#589864

Ramesh..