I am beginning a project that will require java, xml schema and relational db interaction. I'm hoping for some expert opinion on tools and/or approaches to use.
The project will be saving data from a web service in a db schema that should 'match' the xml schema of the web service. So, question one: is there a tool that will generate ddl for a relational db given an xml schema as input?
The db contents will be used in its original xml form as well as by applications that know nothing about xml and only need to access bits of data from the xml. Question two: should I be looking for a db solution that allows xml to be stored and queried? This seems to me to be the best solution but I don't know the state or usefulness of this approach. In addition at least two rdbms, Oracle and Postgres, will be supported so a cross platform solution would make the most sense.
Finally, in some circumstances we will have create dummy xml messages for the cases where we have gotten ahead of the web service. The actual data will come from analysts in the form of a spread sheet or text file. Is there a tool that, given a schema, will generate some sort of Java (or other) UI to allow a schema compliant document to be entered as discrete fields with no XML knowledge required? This tool should eventually produce an XML document from the input.
This question has a few parts to it - I'll do my best to hit all of it. There are numerous approaches the type of problem you are trying to solve and I'll start with how to store things in the database - i.e. should you be storing the data in XML? This really depends on your needs, but I'd lean toward "no" in this case. Things to consider are how much XML will you be storing in the database? XML is verbose, so storing it in a database could grow quickly (that may or may not be a concern for you). But, in planning for change, think about the consequences if and when the XML changes.
There are types of tools known as Object Relational Mapping (ORM) tools, such as Hibernate and TopLink, which are used to map a Plain Old Java Objects (POJO) to a database. So when the XML schema changes, you change the tools that control the mappings between the database and the Java objects rather than the data itself.
As for supporting multiple RDBMS. The key here is abstraction. Don't tie your core code to any particular database implementation but use a pluggable layer that can switch the database (so the core code would only know about a data access interface).
Lastly, for generating dummy XML messages. You may want to look into Altova XML Spy. It's not free, so there may be open source alternatives.