Reading from XML File in Oracle Data Integrator - Part2

Reading from XML File in Oracle Data Integrator - Part2

In my last post part-1 of this series Write to XML, I showed how to reverse engineer xml chema  into ODI and write table data to XML File. In this post, I will continue the part 2 of the series and show how to read data from XML file and load to external tables.

If you are just looking for information just to read from xml file, the pre-requisite is to complete the topology and data model configuration in the part 1 of this series  Write to XML before continuing this tutorial .

So lets get started...

Since we have the topology and model already configured in part1, we just need to load the xml file to XML_STAGING schema and then use table to table mapping for the tables from xml_staging to target tables.

I am going to use data_target schema (new schema created) as target schema and i will be creating the region, countries, locations, departments and employees table in this schema without data. We will load data to these tables from XML. Here are the DDLs for these tables. Please note here that I am continuing with country_num new columns created for country and location tables as I showed in part 1.

CREATE TABLE "DATA_TARGET"."REGIONS"
  (
    "REGION_ID"   NUMBER NOT NULL ENABLE,
    "REGION_NAME" VARCHAR2(25 BYTE)
  )

CREATE TABLE "DATA_TARGET"."COUNTRIES"
  (
    "COUNTRY_ID"   CHAR(2 BYTE) NOT NULL ENABLE,
    "COUNTRY_NAME" VARCHAR2(40 BYTE),
    "REGION_ID"    NUMBER,
    "COUNTRY_NUM"  NUMBER(16,0)
  )

CREATE TABLE "DATA_TARGET"."LOCATIONS"
  (
    "LOCATION_ID"    NUMBER(4,0),
    "STREET_ADDRESS" VARCHAR2(40 BYTE),
    "POSTAL_CODE"    VARCHAR2(12 BYTE),
    "CITY"           VARCHAR2(30 BYTE) NOT NULL ENABLE,
    "STATE_PROVINCE" VARCHAR2(25 BYTE),
    "COUNTRY_ID"     CHAR(2 BYTE),
    "COUNTRY_NUM"    NUMBER(16,0)
  )
CREATE TABLE "DATA_TARGET"."DEPARTMENTS"
  (
    "DEPARTMENT_ID"   NUMBER(4,0),
    "DEPARTMENT_NAME" VARCHAR2(30 BYTE) NOT NULL ENABLE,
    "MANAGER_ID"      NUMBER(6,0),
    "LOCATION_ID"     NUMBER(4,0)
  )
CREATE TABLE "DATA_TARGET"."EMPLOYEES"
  (
    "EMPLOYEE_ID"  NUMBER(6,0),
    "FIRST_NAME"   VARCHAR2(20 BYTE),
    "LAST_NAME"    VARCHAR2(25 BYTE) NOT NULL ENABLE,
    "EMAIL"        VARCHAR2(25 BYTE) NOT NULL ENABLE,
    "PHONE_NUMBER" VARCHAR2(20 BYTE),
    "HIRE_DATE" DATE NOT NULL ENABLE,
    "JOB_ID"         VARCHAR2(10 BYTE) NOT NULL ENABLE,
    "SALARY"         NUMBER(8,2),
    "COMMISSION_PCT" NUMBER(2,2),
    "MANAGER_ID"     NUMBER(6,0),
    "DEPARTMENT_ID"  NUMBER(4,0)
  )

The process of loading xml data to data_target involves creating 3 steps.

1. Create a Procedure in ODI and issue a command to synchronize data from xml to xml_staging schema (Yes. All it needs is a single command to load xml to xml_staging schema)
2. Create interfaces for table to table loading of regions, countries, locations, departments and employees from xml_staging to data_target.
3. Create a package to sequence these objects and run the package.

I have created a new folder XML_TO_TABLE under PRJ_TEST project. All objects we create in this post go inside this folder.

1. Loading XML to XML_STAGING schema

Under xml_to_table folder, right click on procedures and select new procedure. Provide a name and select XML as technology.

Go to details tab and add new step line. Provide a name, select XML technology and select xml logical schema. In the command section provide SYNCHRONIZE FROM FILE and save.

SYNCHRONIZE FROM FILE - This command instructs ODI to load the data from xml file (directory and xml file name defined in toplogy) and synchronize the data to odi_staging schema.

If we need to load new xml files every time, place the xml file with same name as we defined in topology in the same directory we defined toplogy and then run the procedure with this command by selecting the xml logical schema. Boom... xml data will be synchronized to xml_staging.

If we need to load big xml files, external database schema should be used for good performance and debugging of data if needed in the xml jdbc url.

2. Loading from XML_STAGING to Target tables

we need to create interface for regions, countries, locations, departments and employees. The procedure is same for all interfaces. I will show mapping and flow tab for regions and mapping for the interfaces here.

Create regions interface. Provide name

for regions, go to mapping tab, put the xml_staging regions as source and data_target regions as target and do the mapping

In the flow tab, select IKM SQL to Oracle

Lets create interface for countries. In the mapping tab, put xml_staging countries as source and data_target countries as target. I will have introduce a lookup table (green color) since I need to populate the varchar country code (because of country_num i added before). The lookup table is countries from HR schema joined with country name. Lookup tables can be added by selecting the search like icon in the mapping tab above source tables area.

This is the look up table join condition used in countries.

Lets create locations interface. Drop xml_staging location table in source and data_target locations table in target and do the mapping. here also i used data_target countries table we populated in previous step just to populate varchar country_id. The join condition is based on the country_num.

Lets create the departments interface. Drop xml_staging departments table into source and data_target departments table into target and do the mapping in mapping tab.

Lets create the employees interface. Drop xml staging employees into source and data_target employees into target and do the mapping in mapping tab. For the data_hire column, I have used to to_date and substtr functions to convert incoming varchar data into date.

Lets create a package to put everything together. Create a package and put procedure, interfaces of regions, countries, locations, departments and employees in sequence.

Lets run the package and see the results. Package run is successful

Lets check the xml_staging table and target table to see the results. I am just showing employees table from xml_staging and data_target.

With this we have completed the loading xml file to ODI. Please post if you have any questions or suggestions.