Oracle ODI 11g ELT - Oracle DB to XML

Oracle Data Integrator (ODI) is Oracle's data integration platform engineered to provide fast and easy data transformation and data integration processes, through E-LT (Extract - Load Transform) mechanism, between different technologies.  For example, using ODI's transformation mechanism I can create daily snapshots of my data residing in an Oracle DB and store these snapshots in a XML format.  These XML snapshot files can serve as XML payloads for an external web-service to consume this data.  Alternatively, I can use the same mechanism to create file-system based daily backups of my data in a defined XML format.  Oracle DB and XML are just two of the many technologies that can be used within ODI to do data transformations.

This blog assumes you have some basic prior knowledge about the ODI tool.  If you are new to ODI, you can refer to the ODI Tutorial for a quick introduction or the ODI Develop Guide for comprehensive details on the specific features of the ODI tool mentioned below. For this demonstration, I am using the sample HR schema within Oracle XE as my Oracle DB source. I will be doing a data-dump from the Employees table of this schema to a XML document.

1. Create Oracle Physical and Logical Architecture.

Under Topology, create your Oracle Data Server, its physical schema, and its logical schema.

My LOCAL Oracle XE

Physical Schema Data Server

oracle logical schema

2. Prepare your XSD and sample XML document.

Create the XSD for the format you will want your data to be displayed in your XML. 

schema code sample

schema xml code sample

3. Create XML Physical and Logical Architecture.

Under Topology, create your XML Data Server, its physical schema, and its logical schema.

oracle data server

JDBC Driver URL

There is a known issue with ODI 11.1.1, 40.4.1 SQL Exception "Unknown Token" appears when using Complex File or XML as Staging Area. Details can be found here. Based on the workaround provided by Oracle, changes have been highlighted in the image below.

Oracle Physical Schema

Oracle Logical Schema

4. Create Oracle DB Model.

Under Designer, in the Models section, create your Oracle DB Model, using the Oracle Logical schema you already created.

Oracle HR1

5. Create XML Model.

Under Designer, in the Models section, create your XML Model, using the XML Logical schema you already created.

XML Model 1

Selective Reverse Engineering

Models

6. Create Project.

Now that you have your Models created, you have to create a new ODI project to be able to create your Interface to do the ELT, using those Models. Under Designer, in the Projects section, create a new Project.

Sample project

7. Import Knowledge Modules.

The two KMs you will need for the ELT are LMK SQL to SQL and IKM XML Control Append. Import them into the project. Full description of these KMs can be found in the Dev Guide mentioned earlier.

oracle import known modules

8. Create Interface.

You will need to create your Interface to map data-elements between your Source and Target Models. Under your project, under Interfaces, create a new Interface.

oracle create interface

On your Interface, in the Mapping section, design your mappings between the Source and Target Models.

interface mapping

On your Interface, in the Flow section, select your Source (Oracle DB) and assign the LMK SQL to SQL.

interface flow mapping

On your Interface, in the Flow section, select your Staging Area and assign the IKM XML Control Append and set the CREATE_XML_FILE = true.

create xml file oracle

On your Interface, in the Flow section, select your Target (XML) and assign the IKM XML Control Append and set the CREATE_XML_FILE = true.

create xml file

9. Save All and Execute Interface.

Save All and you are done. When you execute your Interface, your XML document (Target) with the data from your Oracle DB table (Source) should be created.

xml file output

2 minute read