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
I want an example of a more complex xml, with 10 tables when revers in odi.
Can you halp me?
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:element name="employeeX" maxOccurs="unbounded">
<xsd:element name="firstname" type='xsd:string'/>
<xsd:element name="lastname" type='xsd:string'/>
<xsd:element name="email" type='xsd:string'/>
<xsd:attribute name="empid" type='xsd:int'/>
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.
I have resolved the above issue by adding sequence number in the unmapped column of the xml target datastore
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.
I am getting 'Statement is not in Batch mode' when running the interface. I am using 'ODI 11g'.
Does anyone faced this issue before?
Plz post some coding level approach.... thanks in advance...
I am looking for an example that reading data from table and preparing xml payload and publish to JMS queue.
Please help me....
Could you please share xsd and xml files for HR schema for multipul tables to xml file?
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.
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.
Thanks for the details explanation.
Issue- After executing the interface the status is going to success but rows are not getting inserted