XML & RDBMS auto mapping?


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.

4 comments

  1. For the GUI to provide the mapping features, it needs to explore and understand the XML and RDBMS schema. Thats the whole point of discussion above. If a tool can explore the schemas then it can also do a simplisitic/standard mapping.

  2. Hi , I have similar kind of task, but small difference that XML file needs to be process bi-weekly. Basically that XML file reside on other system and I need to pick(read) that file and processed with some validations and insert into my RDBMS tables.

  3. Kumar, your task is a recurring one and i can safely assume the xml file format remains same all the time. So i would suggest to go for one of the approaches mentioned above.

    On a side note, you may even want to automate the whole task and schedule it to run on pre-specified time, to eliminate any human error and make the process more efficient.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s