Help with XML as source

[bgroh]

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

[rareddy]

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

[bgroh]

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

[jdoyle]

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