Example - XML to Table using ODI

How to load data from XML to Table using ODI 12c?

Source File:
<?xml version="1.0" encoding="UTF-8"?>
<employeeDetails>
<employeeInfo>
<age>28</age>
<name>Trinesh</name>
</employeeInfo>
<employeeInfo>
<age>27</age>
<name>Shanthi</name>
</employeeInfo>
<employeeInfo>
<age>29</age>
<name>Tejesh</name>
</employeeInfo>
<employeeInfo>
<age>25</age>
<name>Vasavi</name>
</employeeInfo>
<employeeInfo>
<age>19</age>
<name>Nishith</name>
</employeeInfo>
</employeeDetails>

Target Table Structure:
CREATE TABLE REPLICA.EMPINFO
(EMPID NUMBER(3),
NAME VARCHAR2(50),
AGE NUMBER(3));

Pre-requisites:
Knows how to do topology configuration for Oracle Technology
Knows how to create model for Oracle

Steps:

Topology Configuration

•  Expand Physical Architecture --> expand Technologies --> Right click on XML Technology and create new Dataserver
• In the definition provide the name of Dataserver (for e.g., XML_DS)
• Go to JDBC and Select as mention below

• For JDBC URL: jdbc:snps:xml?f=<filename>[&s=<schema>&<property>=<value>...]

For example: jdbc:snps:xml?f=G:\ODI_FILES\ODI_XML\employee.xml&re=employeeDetails

Property	Description
f	XML file name. Use slash "/" in the path name instead of back slash "\". It is possible to use an HTTP, FTP or File URL to locate the file. Files located by URL are read-only.
d	This file may be a DTD or XSD file. It is possible to use an HTTP, FTP or File URL to locate the file. Files located by URL are read-only. Note that when no DTD or XSD file is present, the relational schema is built using only the XML file content. It is not recommended to reverse-engineer the data model from such a structure as one XML file instance may not contain all the possible elements described in the DTD or XSD, and data model may be incomplete
re	Name of the element to take as the root table of the schema. This value is case sensitive. This property can be used for reverse-engineering for example a specific message definition from a WSDL file, or when several possible root elements exist in a XSD file.
ro	If true, the XML file is opened in read only mode
S	Name of the relational schema where the XML file will be loaded. If this property is missing, a schema named after the five first letters of the XML file name will automatically be created. This schema will be selected when creating the physical schema under the XML data server.
cs	Load the XML file in case sensitive or insensitive mode. For case insensitive mode, all element names in the DTD file should be distinct (For example: Abc and abc in the same file will result in name collisions)

• Save it and test the connection. You will get successful connection prompt
• Right click on Data server and create Physical Schema (Provide the first five characters of file name if you didn't provide property of s in JDBC URL)

Logical Architecture

• Expand Technologies -- Expand XML -- Right click and new logical Schema (XML_LS)

Context

• Link Logical Schema to Physical Schema

Designer:

Model 

• Create Model for XML technology as mention below

• Right click on Model and say click on reverse Engineering
• It will create two datastores as mention below

The standard reverse-engineering process will automatically reverse-engineer the table from the relational schema generated by the XML driver. Note that these tables automatically include:

Primary keys (PK columns) to preserve parent-child elements relationships

Foreign keys (FK columns) to preserve parent-child elements relationships

Order identifier (ORDER columns) to preserve the order of elements in the XML file

Project:

• Import Knowledge modules - LKM SQL to SQL  and IKM SQL Control Append
• Create mapping -- Drag drop the XML datastore (EMPLOYEEINFO) and Table datastore from Oracle Model (EMPINFO) and auto map it.
• For Employee Id map to the EMPLOYEEINFOORDER
• Go to physical tab and select above mention KM's
• Execute the Mapping

Output: