September 23 2011

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.

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. 

3. Create XML Physical and Logical Architecture.

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

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.

4. Create Oracle DB Model.

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

5. Create XML Model.

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

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.

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.

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.

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

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

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.

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.

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.

Join the Conversation

January 30, 2014

hello!

I want an example of a more complex xml, with 10 tables when revers in odi.

Can you halp me?

July 4, 2014
March 16, 2015

Hi,

I have created the interface as the above instructions. There no table data being loading in xml file. 

my xsd is

<?xml version="1.0" encoding="windows-1252"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
        targetNamespace="http://www.xyz.com/emp"
        xmlns:tns="http://www.xyz.com/emp"
        elementFormDefault="qualified">
        
    <xsd:element name="employeesX">
     <xsd:complexType>
       <xsd:sequence>
        <xsd:element name="employeeX" maxOccurs="unbounded">
          <xsd:complexType>
          <xsd:sequence>
            <xsd:element name="firstname" type='xsd:string'/>
            <xsd:element name="lastname" type='xsd:string'/>
            <xsd:element name="email" type='xsd:string'/>
               
      </xsd:sequence>
           <xsd:attribute name="empid" type='xsd:int'/>
     </xsd:complexType>
    </xsd:element>
   </xsd:sequence>
  </xsd:complexType>
 </xsd:element>
</xsd:schema> 

 

jdbc url jdbc:snps:xml?f=C:/emp5.xml&d=C:/emp5.xsd&re=employeesX&s=EMPX5

 

The data gates loaded in the model xml table. 

Thanks.

 

 

March 17, 2015

I have resolved the above issue by adding sequence number in the unmapped column of the xml target datastore

March 17, 2015

I am now trying to load blob datatype column in xml.

It is giving error

8000 : null : java.sql.SQLException: incompatible data type in conversion
java.sql.SQLException: incompatible data type in conversion

 

Please can I know how to load blob in xml.

 

April 15, 2015

I am getting 'Statement is not in Batch mode' when running the interface. I am using 'ODI 11g'.

Does anyone faced this issue before?

THANKS

November 3, 2015

Plz post some coding level approach.... thanks in advance...

 

November 15, 2015

Hi,

I am looking for an example that reading data from table and preparing xml payload and publish to JMS queue.

Please help me....

 

December 11, 2015

Hi Friends,

Could you please share xsd and xml files for HR schema for multipul tables to xml file?

Thanks ,

Madhav

Ravi
June 21, 2016

Hi,

Thanks for detailed explanation - the outpout file you generated doesn't have xsi:schemalocationdetails.

please let me know what setup is requied to add this.

SNAPSHOT with ODI
January 24, 2017

Hello

Could you please provide me the standard function of ODI whitch provide the snapshot. I need to make a snapshot to my data store where I make a change and I need to have a photo of the situation before the change.

Thank you

jyothi
March 14, 2017

Hello,

 

Thanks for the details explanation.

Issue- After executing the interface the status is going to success but rows are not getting inserted 

Rows are not getting Inserted

Enter your first name. It will only be used to display with your comment.
Enter your email. This will be used to validate you as a real user but will NOT be displayed with the comment.
By submitting this form, you accept the Mollom privacy policy.