Get metadata for xml data source in teiid 8.

[akshy_harale]

I using following approach to get the metadata of the xml data source. But it gives empty result set.

DatabaseMetaData dbMetaData=connection.getMetaData();
               ResultSet rs=dbMetaData.getTables(null, "MarketData", null,types);
               //ResultSet rs=dbMetaData.getTableTypes();
               while(rs.next())
               {
                    String tabName=rs.getString("TABLE_NAME");     
               }

my VDB as

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<vdb name="port" version="1">  
          <description>A Dynamic VDB</description>  
          <property name="UseConnectorMetadata" value="cached" />   
          <property name="UseConnectorMetadata" value="true" /> 
          <model name="MarketData">
       <source name="text-connector" translator-name="file" connection-jndi-name="java:marketdata-file"/>
    </model>
</vdb>  

and bellow you will get the xml file

[shawkins]

Akshay,

A file translator only exposes procedures for getting at file contents - see the File Translator under https://docs.jboss.org/author/display/TEIID/Built-in+Translators

Steve

[rareddy]

Akshay,

Also see this for working with Text based data. https://community.jboss.org/wiki/TextToTableWithTeiid Note that from the time we wrote the article and now, you can even create the view definition in the dynamic vdbs using DDL. see https://docs.jboss.org/author/display/TEIID/DDL+Metadata there is an example in the 8.1 examples section too.

Ramesh..

[akshy_harale]

Thanks for quick reply.

Ranmesh I able to get the data from xml file but I want to get the metadata for the xml db which returning the empty result set of tables

ResultSet rs=dbMetaData.getTables(null, "MarketData", null,types);

"rs" object in this case contains the empty set.

[rareddy]

Akshay,

Ok, there is another concept called XMLTable, using that you can convert a XML data into relational table, once you convert into table, then if you do the above metadata call on the view model you will get the metadata. See https://docs.jboss.org/author/display/TEIID/FROM+Clause#FROMClause-XMLTABLE

Ramesh..

[akshy_harale]

I did that tutorial posted by you https://community.jboss.org/wiki/ConvertXMLDataIntoRelationalTableDataUsingTeiid. I successfully retrieved the contents in XML file. METADATA is not available.

[shawkins]

Akshay,

You are defining the table metadata.  If you create a view that uses an XMLTABLE, then there will be metadata for that view with the schema name matching the model name.  Otherwise you will only have procedure metadata from the file translator.

Steve

[akshy_harale]

Steven,

Can you please elaborate your solution considering that I have employee.xsd and employee.xml containing data for some 50 employees.

[shawkins]

Akshay,

The initial vdb you showed above gives you access to files.  You have to add an additional model to that vdb containing a view defined by the SQL to read the file and extract the desired columns - as shown in the tutorial.  Once you have a view defined, then you will have table metadata.  You can also use teiid designer's xml relational wizards to help you define the necessary XMLTABLE extraction.

Steve

[akshy_harale]

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<vdb name="wellVdb" version="1">

    <property name="preview" value="false"/>

    <model name="wellview" type="VIRTUAL" visible="true" path="/SampleXMLTeiid/wellview.xmi">

        <property name="checksum" value="856954538"/>

        <property name="modelClass" value="Relational"/>

        <property name="builtIn" value="false"/>

        <property name="indexName" value="1794206580.INDEX"/>

        <property name="imports" value="/SampleXMLTeiid/wellSrcModel.xmi"/>

        <validation-error severity="WARNING" path="wellsView">Possible cross-join: Group/s '[f, A]' are not joined either directly or transitively to other groups through a join criteria. Check all queries in the transformation.</validation-error>

    </model>

    <model name="wellSrcModel" type="PHYSICAL" visible="true" path="/SampleXMLTeiid/wellSrcModel.xmi">

        <property name="checksum" value="835124528"/>

        <property name="modelClass" value="Relational"/>

        <property name="builtIn" value="false"/>

        <property name="indexName" value="4039519328.INDEX"/>

        <source name="wellSrcModel" connection-jndi-name="wellSrcModel" translator-name="wellSrcModel_file"/>

        <validation-error severity="WARNING" path="getTextFiles/Result/filePath">Missing or invalid Length on column with a string/character datatype  (See validation Preferences)</validation-error>

    </model>

    <translator name="wellSrcModel_file" type="file" description=""/>

</vdb>

this is the VDB generated by the teiid designer.

But for the VDB in teiid 8 I want to know how to define View saperately.

[onkar.dhuri]

Thanks Steven.

I have the same problem, however I want to get the metadata on the fly from xml itself.

I dont want to hardcode the column names in my view defined by SQL in vdb, as xml can have additional columns over period of time.

Is there any way by which I can access the metadata (columns from xml table) on the fly without have to creating a hardcoded SQL view ?

Can I query the xml using XMLTABLE for the exact columns defined in the same xml ?

Regards,

Onkar

[rareddy]

Akshy,

Looks like we not making much progress here. You first started out with Dynamic VDB, now you switched over to the Designer based VDB. Those two are not equal in terms of how they represent the a VDB's metadata to Teiid engine.

If You are using Dynamic VDB

What you showed in your very first post is, as Steven indicated a way to get to your XML files. Now once you have the XML Files and its data, it needs to be converted into a tabular data using XMLTable syntax in a view model. There are plenty of examples and tutorials on how to do this. Once you have those definitions coded, you can deploy the Dynamic VDB to the Teiid Server and can ask for the VDB's metadata

If you are using Designer

Use Designer's "File -> Import -> Teiid Designer -> File Source (XML) >> Source and View Model" wizard and walk through the wizard's steps to create a XMLTable. Build a VDB with all the models generated and deploy into Teiid Server, and then you can ask for VDB's metadata .

Also, when you say "Get Metadata for XML Data Source" what are you expecting to see? We may have mis-communication as to what we are saying metadata vs your expectation.

Hope this helps.

Ramesh..

[rareddy]

Onkur,

I have the same problem, however I want to get the metadata on the fly from xml itself.

I dont want to hardcode the column names in my view defined by SQL in vdb, as xml can have additional columns over period of time.

If you are looking for way to find the all the elements in a XML file, that is different from what we are calling metadata.  May be one can write XSLT transformation or some thing that can be applied on a XML file which can spit out the XML's element and attribute names.

Is there any way by which I can access the metadata (columns from xml table) on the fly without have to creating a hardcoded SQL view ?

Can I query the xml using XMLTABLE for the exact columns defined in the same xml ?

XMLTable is a language construct that *strictly* builds a relational tabular data from a XML. You can ask Teiid what is the tabular format of the converted document, like what is table name and its columns etc, that is what we are calling as metadata. There is no way to generate the XMLTable construct on the fly based on the XML, as Teiid engine does not have the smarts of business logic as to how one would like to expose this XML as a table as XML documents can get pretty complex with nesting and recursions etc, so this is user driven exercise. Once you have the XMLTable view defined you can treat that table as any other reqular table in SQL with joins, where clauses etc.

Ramesh..