Reversing XML file in ODI

"XML files can be used as tables, yes, that is an old story.
But have we tried doing that in ODI ? no, not until now"

Probably the best use of xml files are that they can be used to store relational table information. I tried reversing xml file which is basically a table in ODI and thus this blog post was born.

Tables in xml format have two files associated with them

• The data contained in the table is stored in the .xml file
• The metadata or column information of that table is contained in another file with the extension '.xsd' or '.dtd'

More information about how .xsd and .xml are created can be found here.

http://www.w3schools.com/schema/schema_example.asp
I will be using a simple xml file which is basically stolen from the internet, here are the contents of the file (note that it is only half of the picture that I have posted, you will have to complete the elements 'book' and 'catalog')

What this file above tells is that there is some table which contains information about books and we need to represent the same in ODI. Lets proceed.

Step 1:

Creating Data Server and Physical schema:

When specifying the JDBC url, we need to mention three things which are required for a successful reverse.
1. xml file name and location
2. dtd or xsd file name and location
3. Schema which will represent the table

In the pciture above I havent mentioned Schema yet (I will come to that later). And where did I get the book.dtd file from ? Well, ODI automatically creates a dtd file (if not specified) when you reverse the model.
All you have to do is specify only the f=../**.xml parameter initially, then create a model in designer and reverse it, this will only generate the dtd file and wont give the tables. You have to come back to topology manager and specify the details of dtd with the d=../**.dtd file

The Schema:
As ODI always does things in a proper DBMS way, it always needs a schema , that is the place holder of all tables, to be specified. When creating a Physical schema after this step, You will see that ODI has automatically created a schema as BOOK based on the DataServer name 'BOOK_TRIAL'

Now, go back to the DataServer details and enter the schema parameter like this

Test the connection after saving, if all goes well, its time to switch to the designer window

Step 2:

Creating Model :

Just like we create a model for any other source, create a model ... when reversing, use Standard reverse and and go to Selective Reverse option and check 'Objects to reverse engineer', it will give you the details of tables available

Check the desired tables and click reverse engineer button, you will soon see the two tables created under that Model

Voila !!! as you can see the contents of BOOK table are those that I showed in the xml file.
Best of luck with your integration !!!

Next, I am going to try converting Relational tables to xml using ODI. Will keep everyone posted