1 2 3 Previous Next 66 Replies Latest reply on Oct 12, 2012 12:35 PM by rareddy Branched to a new discussion.

    From relational to XML (conforming to xschema)

    gioppoluca

      Is it possible to transform a relational table from a physical model to a (complex) XMl conforming to a xschema?

      I've created two physical models one from the xschema and the other from the relationa DB (using import function)

      I've created a view.

      The problem is that creating the mapping is not so easy transformation editor is never reconciled.

      Any hints?

      Version 7.6 of designer

        • 1. Re: From relational to XML (conforming to xschema)
          rareddy

          Luca,

           

          Yes, it should be possible.  There are couple different ways currently available

           

          1) Use the XML document model in Teiid Designer. Create a new Model, choose "Model Class: XML" & "Model Type: View" and then choose the XSD and root element and create the model. Once this is generated, it is your responsibility to add the mapping class transformations that are derived from your source models. I believe there are documents that describe this whole process, check the Designer docs. This is process has some limitations as to criteria processing and some performance implications.

           

          2) Second, is something you already trying. Unfortunately, we do not have the wizard to generate the SQLXML based transformations based XSD yet.  This is manual exercise for now, we hope have feature to generate SQLXML based transformations in future releases.  If you would like you can submit a feature request for this.

           

          Thanks

           

          Ramesh..

          1 of 1 people found this helpful
          • 2. Re: From relational to XML (conforming to xschema)
            gioppoluca

            Thanks for the quick answer.

             

            I'll try the first option.

            Considering that I've a complex XML with recursive data (let's make an example a student and it's courses) how can I map it using the student table and courses table?

            Thanks

            Luca

            • 3. Re: From relational to XML (conforming to xschema)
              blafond

              If an XML element is recursive, the generated mapping class should indicate that as described in the Designe user guide.

               

              See:  http://http://docs.jboss.org/teiid/designer/7.6/user-guide/en-US/html_single/#recursion-editor-section for details.

              • 4. Re: From relational to XML (conforming to xschema)
                gioppoluca

                I'm afraid that the documentation is not too much related to my need (or I did not understand it ... it could be possible).

                My need is this:

                I've a relatinal Db (Oracle) with a bunch of tables, I need to expose a web service that produce an XML document with data from Oracle, but conforming to a given xsd.

                 

                I'm not able to find a simple example on how to do all the mappings from the relational schema to the xschema to produce the correct XML (I'll think at that point on how to implement a web service).

                 

                Any hint?

                Thanks

                Luca

                • 5. Re: From relational to XML (conforming to xschema)
                  tejones

                  Luca,

                   

                  If you have the XSD in your Designer workspace, you can right-click on a target element and click "Modeling->Create XML to Scalar Transformation" to create a SQL transformation that will take XML conforming to the element you've selected and output the scalar values from that XML. "Modeling->Create Scalar to XML Transformation" will perform the transformation in the other direction. All of the required SQL/XML will be generated for you. Would this assist you in your use-case?

                   

                  Thanks,

                  Ted

                  • 6. Re: From relational to XML (conforming to xschema)
                    rareddy

                    Luca,

                     

                    Mapping classes are like view tables, just like in view tables you can define transformations based on the other tables here. The difference is, since the data being created hierarchical, a nested columns need to be passed a input set (filter) from parent so that children can be filtered out for particular parent. I will try to see if there is example I can post on this.

                     

                    Ramesh..

                    • 7. Re: From relational to XML (conforming to xschema)
                      rareddy

                      Luca,

                       

                      Attached  a "how to" guide to do simple XML document from relational table that may help you. Take a look, it is a very simple example. Later you can ask any specific questions.

                       

                      Ramesh..

                      1 of 1 people found this helpful
                      • 8. Re: From relational to XML (conforming to xschema)
                        gioppoluca

                        Thanks Ramesh, the explanation is much clearer.

                        Since my data is a bit more complex I'm finding difficulties in managing it to work.

                        Could you post the xsd and sql you used so that I can understand if I'm following correctly your instructions or I'm missing something around?

                        TIA

                        Luca

                        • 9. Re: From relational to XML (conforming to xschema)
                          pnittel

                          Luca, I've attached the XML schema, and another supporting schema, that were used in the example document. I hope it helps fill in the gaps for you!

                           

                          Cheers,

                          Paul

                          • 10. Re: From relational to XML (conforming to xschema)
                            gioppoluca

                            Thanks for the files.

                            In effect it filled the gaps.

                            Now I'm gettig trouble in deploying the VDB:

                            AS 7.1.1 TEIID 8b2

                            This is the error I get:

                             

                            {"JBAS014653: Composite operation failed and was rolled back. Steps that failed:" => {"Operation step-2" => {"JBAS014671: Failed services" => {"jboss.deployment.unit.\"libri.vdb\".PARSE" => "org.jboss.msc.service.StartException in service jboss.deployment.unit.\"libri.vdb\".PARSE: Failed to process phase PARSE of deployment \"libri.vdb\""}}}}

                             

                            Maybe is off topic for this thread, but there could be thing that are tied to this thread that gets missing.

                            Do I have to deploy also the xsd?

                            Thanks

                            Luca

                            • 11. Re: From relational to XML (conforming to xschema)
                              mdrillin

                              Luca,

                              We have not yet integrated Teiid Designer with AS 7.1.1 TEIID 8.0 B2.   You will need to use the AS 5.1 - based Teiid.  Latest version is Teiid 7.7. 

                              Thanks!

                              Mark

                              • 12. Re: From relational to XML (conforming to xschema)
                                gioppoluca

                                Are you saying that is not possible to test teiid 8b2 since no vdb created with designer manage to deploy?

                                Is there any workaround to this?

                                Luca

                                • 13. Re: From relational to XML (conforming to xschema)
                                  rareddy

                                  You can create VDB in the designer, however you can not use preview or an other feature to create data sources etc from designer 7.7  into Teiid 8.0. You can manually copy the VDB into the Teiid 8.0 runtime following its documentation as to how to deploy, then your VDB does work.

                                   

                                  The compatible Teiid Designer 8 with Teiid Runtime 8 is not available as of today, but it is in works.

                                   

                                  Ramesh..

                                  • 14. Re: From relational to XML (conforming to xschema)
                                    mdrillin

                                    You can create the VDB in designer, then use the Teiid admin tools to test deployment.

                                    Mark

                                    1 2 3 Previous Next