3 Replies Latest reply on Apr 12, 2011 10:40 AM by John Doyle

    Help with XML as source

    Bernd Groh Newbie

      Hi there.

       

      I seem to be having issues succesfully querying XML as source. I've imported the schema, created the relational view model from the schema, and it did create all the tables I was expecting, yet, the select statement seems to read:

       

      SELECT * FROM

       

      This now gives me parsing errors. So, what exactly am I missing?

       

      I've read the article here:

       

      http://community.jboss.org/wiki/ConvertXMLdataintoRelationalTabledatausingTeiid

       

      Referring to SQL as follows:

       

      select employee.* from (call department.getTextFiles('*.xml')) f,
         xmltable('$d/dept/employee' PASSING xmlparse(document f.file) as d COLUMNS
             empID     integer     PATH '@id',
             firstname     string     PATH 'name/first',
             lastname     string     PATH 'name/last') AS employee

       

      Is that the type of query I need here? If there's something I still need to do here in order to have the Designer create the according SQL, what exactly is it? And am I right in assuming that I don't need to do this manually for each of the tables?

       

      Any help with this would be much appreciated.

       

      Cheers,

      Bernd

        • 1. Help with XML as source
          Ramesh Reddy Master

          Bernd,

           

          The Designer dos not automatically add the above SQL transformation to create view creation, so you would need to add something similar to above in your view definition. You would need to do this for each view definition.

           

          If you are working with WSDL there is automation (Import WSDL Operation) available in the Designer based on the request and response documents, but for plain XML you are on your own to define the view to fit your needs.

           

          Ramesh..

          • 2. Help with XML as source
            Bernd Groh Newbie

            Thanks Ramesh.

             

            Don't work with WSDL, only have the XSD. Was hoping for some more automation I guess, but if that's how it is, that's how it is.

             

            Cheers,

            Bernd

            • 3. Help with XML as source
              John Doyle Apprentice

              There is some automation around XSD.  Designer has some actions that can create the procedures from you from the XSD.  You should first create the virtual model that you want the procedures in, then open the XSD in the standard eclpse XSD editor.  You can then right click an Element or Type in the XSD visual editor or hierarchy view and some Teiid options will display for creating procedures.

               

              ~john