Recently i was given a problem to solve. Its not an uncommon problem neither is the solution very tricky but the team facing the problem was so constrained on time that none of the proven solutions seemed like ‘a solution’.
The problem :-
I have an XML data file that needs to be populated into a set of RDBMS tables. The XML is nested upto three levels deep and i didn’t get a chance to ask for the table structure.
The Solutions :-
I know what you are thinking – its a very simple problem. We face it day in day out. Agreed. Lets see what solutions come to mind immediately,
>1> Bind XML to Java objects and persist them using JDBC.
There are utilities like JAXB, Castor, XmlBeans which help in converting between XML and Java. They either work on a mapping file or take XML Schema as input to generate the Java classes. If you know you java classes then you map them into XML nodes and elements or you use the XML schema to generate java classes. Once you have unmarshalled your XML into Java you can either use JDBC or any OR mapping solution to persist them into relational tables.
>2> Use tools to map XML and Relational tables.
There are a lot of open source (e.g. DBMT, JDBCImporter, myXDM) and commercial tools (e.g. Altova MapForce) available which promise to relieve you of the coding by allowing you to map XML schema to Database schema directly. The tools then use that mapping as information and perform the conversions.
>3> Use the RDBMS’ XML toolsets
Realising the importance of XML as data communication channel some databases have started providing tool kits to help perform many trivial tasks related to XML and RDBMS. Based on your target database you should lookout for such toolkits too. Oracle provides XDK which can do conversion provided the input XML adheres to Oracle’s defined schema. This means i need to do XSL transformation on my input XML to generate the Oracle specified XML format to persist it into tables.
These three solutions came to my mind and i proposed too. But if you notice all of them need some kind of coding or XML mapping. But the team was in such a time schedule that it had no bandwidth and CPU cycles to perform even this task. This is where i felt, there should be a tool which could explore the XML schema and database schema and auto map. This need not be a 100% correct mapping but even if it does some percentage, it gives the team a start-up.