In the process, XMLType tables and nested tables are also automatically created as necessary to back up thoses collections ( = Object-Relational storage). When you register the XSD, Oracle creates a set of object and collection types that map the XML data model to the SQL data model. Oracle already provides such a mechanism out-of-the-box (called Object-Relational storage), but you cannot use it the way you think. In order to do this automatically, you have to design and maintain a mapping mechanism between the source data (relational tables or views I presume) and the structure defined by the XML schema. How do you intend to "provide the source information"? How possible it is, depends on various factors, including how complex your XSD's are and how much effort your company wants to put in, or whether an existing tool can do it for you.īut now instead of this I was asked to generate XML Just by using XSD and provide the source information to generate XML. Of course, if your XSD is less complex, then existing tools should be able to assist you.
That meant that we could get new XSD's and evaluate the impact of what had changed within a single work day and we know that other suppliers having to supply the same structured data, using their own tools, were having to spend weeks evaluating and determining what needed to change in their code (we had dealt with some of them to see how they were going about the same task) Perhaps the hardest part of that was writing the parser to handle the complexity of the XSD's, but once we had that, when the supplier changed the XSD's it was simply a case of running the new ones through the parser, getting the new database creation scripts that it created, and doing a database comparison between the existing and new database structure to see what needed to be changed in the code that mapped the data. In the end, I wrote an XSD parser in PL/SQL to read the XSD's, flatten the structure to relational database tables (creating me a database creation script), create a package that could be used to read the data from those tables and generate XML (using standard SQLX functionality) and also create a skeleton package that provided the appropriate structure of functions to enable us to query the data we needed and insert it to the relational tables ready to be generated as XML. Some couldn't read the XSD's because they were too complex for them, but could generate XML ok.
Some could read the XSD's and create a database structure, but failed to be able to generate the XML output.
From all the tools we looked at, none could do the full thing or handle the complexity of the XSD's we needed.
We reviewed all the tools available at the time for doing the ETL process and were looking for something that could read the structure of the XSD's, create database tables to reflect them that we could then populate with data and then have it generate the XML in that same structure (we reviewed around 20 different products from free tools to top of the range expensive ones). However these XSD's were very complex (several XSD files, referencing each other and very deep and complex structures). I worked on a project (and still support it) a few years ago where a 3rd party supplied the XSD describing the XML data structure we needed to supply. I want to know whether automatic generation of XML Files using XSD is feasible in Oracle and if so whatever I have mentioned above is a feasible route or is there any other approach. Question for here here is I am just thinking about this approach and yet to try. Once the tables are created then I can create a process to load the data into those tables and then extract the XML from those tables. If I register an XSD inside the database, it can create tables for the underlying XSD. I was thinking of an approach on this one.
The reason behind this ask is suppose if an XSD changes then there should not be much change. It sounds strange as now the ask is that XML has to be generated with data automatically using the xsd.
But now instead of this I was asked to generate XML Just by using XSD and provide the source information to generate XML. But in all those places I am using the table data and building my XML basing upon what is asked for. I worked a lot on generating XMLS using XMLForest, XMLAGG function, XMLDOM packages. I got a requirement where I was asked to generate XML from an XSD and populate the data automatically.