7 Replies Latest reply: Sep 20, 2010 10:03 PM by jalen zhong RSS

Can Teiid support using XSD as View and DB as Source?

jalen zhong Newbie

I am evalating Teiid in our product.

 

I hava two models: one for XSD Schemas, the other for a physical DB tables. I want to use the XSD as the VIEW MODEL and the DB as the SOURCE MODEL so that I can retrieve/update data through the VIEW MODEL. Is that feasible?

 

If it can do that, is there any example to define it in the Teiid Designer?

  • 1. Re: Can Teiid support using XSD as View and DB as Source?
    Ramesh Reddy Master

    You can import XSD file, then create a "XML" view model that represents the XSD. The data can be provided to this model, through any other source models. This eventually produces a XML document that is based the XSD you provided, that you can query through Teiid JDBC API.

     

    However, you can not do a INSERT into a XML document (view).  It only supports "SELECT", that returns a XML document. Check out Designer's help for using the XML view model.

     

    I am not even sure I understand what you are trying to do. A XSD is schema, it defines a structure for your XML file. If you are building the contents of the XML file from contents from DB, then what is reason not to insert/update DB directly?  How are you intending to send the update? If you are looking for abstraction layer, you can do that with out XSD. May be explain what is that are trying to achieve then I can possibly point in right direction.

     

    Hope this helps.

     

    Thanks.

     

    Ramesh..

  • 2. Re: Can Teiid support using XSD as View and DB as Source?
    jalen zhong Newbie

    Sorry for the late response. I just come back and can access the internet today.

     

    Sorry for not explaining my question clearly.

     

    I use a XSD to define a data model structure. I also have a Database schema. The XSD defined model is different with the Database one. For my customer, they can only see the virtual database which is defined as XSD model. So Insert/Update/Delete/Select are all based on the XSD model, but the real implementation is on the database whose schema is different with the XSD model. The difference between the XSD model and Database schema (the mapping) will be done by using the Teiid Designer. And the XSD model is complex so cannot be created manually.

     

    That will be:

    Insert data --> XSD model --> Database

    ...

     

     

    I will explain what I have done using the designer.

    1) Import the XSD schema as a Model

    2) Import the database tables as a Model

    3) Create a source Model using Model Class "Relational" and Model Type "Source Model" (Actually I also using the Model Class "XML" and Model Type "View Model")

    4) Here I met the problem. I want to define the mapping between the XSD and the Database tables. But I cannot drag the tables and XSD item to this view (I want to use this view as a mapping whose "VIEW" is XSD model and "SOURCES" is Database tables)

     

    Am I wrong for anything? Many thanks for your great help!

  • 3. Re: Can Teiid support using XSD as View and DB as Source?
    Ramesh Reddy Master

    Jalen,

     

    OK, now I understand what you are trying to do. Let me explain how you can use Teiid further.

     

    XSD model only can be used to create a XML View model. Once you import the XSD as model, then you create a "xml" model. During the creation of this model, you can select the "xsd" as schema for the "xml" model you are trying to create. Then this process will generate a XML model based on your XSD. Now, you can define the transformations in the generated XML mapping classes using tables from the other database you have imported. After you completely provide all the transformations, you can issue a SQL command against the XML table, which will return the data from your database in XML format that conforms to XSD you defined. However, this table does not support inserts or updates. Only "select".

     

    What I understand from your response is you want to submit a document in given XSD format to Teiid, and that does the mapping of data to the database. Unfortunately, Teiid does not support any auto-generation of the model in this case. However, if you want you can accomplish this with Teiid. Here is how

     

    1) Create  a relational view model in Designer. Then add virtual procedure that takes XML as input.

    2) Write the virtual procedure, you can use XML Functions to extract the data from the XML input and write whatever insert/update/delete statements in the procedure to the database. Especially look at XMLQuery and XMLTable, these can take a XML input and convert to the relational data. Then use the Teiid virtual procedure language to cursor through the data and insert into database.

     

    You can look for examples of how to use XMLQuery on the web as this SQLXML standard.  For XMLTable see this.

     

    Ramesh..

  • 4. Re: Can Teiid support using XSD as View and DB as Source?
    jalen zhong Newbie

    Remesh,

     

    Thanks for your patience.

     

    XSD schema is used here only for creating a data structure (just like "base table") in the view in Teiid so that I can define my transformation between it and database tables. After loading the "base table", the XSD file is no use anymore. All operations are done on the these "base table".

     

    It looks like:

    "base table" in view (done by loading the XSD) --> transformation --> database table

  • 5. Re: Can Teiid support using XSD as View and DB as Source?
    Ramesh Reddy Master

    Teiid does not provide any tools to convert XSD into relational model, to use for inserts and updates. Since you do not use XSD after creation of tables, you are free to define the view that fits your XSD model as you use it in this situation.

     

    Ramesh..

  • 6. Re: Can Teiid support using XSD as View and DB as Source?
    Ramesh Reddy Master

    Correction, after looking into the Designer little more, there is option to convert XSD to a "relational" model. For this to happen

     

    1) import the xsd file

    2) right click on the XSD file, and choose "Modeling->Create Relational View Model from Schema"

     

    That does what you are looking for.

     

    Ramesh..

  • 7. Re: Can Teiid support using XSD as View and DB as Source?
    jalen zhong Newbie

    Ramesh, it works and your advice help me alot.

    Thanks again!