Load a simple XML document in ODI

Reference


Let’s create a simple XML file and its XSD.

XML document
<?xml version="1.0" encoding="windows-1252" ?>
<Employee>
<EmpId>325856</EmpId>
<EmpName>Arpit</EmpName>
<EmpSalary>1231231</EmpSalary>
<EmpLocation>Bangalore</EmpLocation>
<EmpDepartment>Oracle</EmpDepartment>
</Employee>

XML Schema
<?xml version="1.0" encoding="windows-1252" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns="http://www.example.org"
targetNamespace="http://www.example.org"
elementFormDefault="qualified">
<xsd:element name="Employee">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="EmpId" type="xsd:integer"/>
<xsd:element name="EmpName" type="xsd:string"/>
<xsd:element name="EmpSalary" type="xsd:integer"/>
<xsd:element name="EmpLocation" type="xsd:string"/>
<xsd:element name="EmpDepartment" type="xsd:string"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>

Now let’s try to load this data into ODI.
Log in to ODI
Go to Topology and Physical Architecture




Select XML and create a new Data Server
Specify a logical name and move to JDBC tab

Choose “com.sunopsis.jdbc.driver.xml.SnpsXmlDriver” as Driver
And JDBC URL as
“jdbc:snps:xml?f=C:\Users\arahi\Desktop\ODIDocs\Employee.xml&s=Employee&ro=true”
Here
f=C:\Users\arahi\Desktop\ODIDocs\Employee.xml is the location of the xml file
&s=Employee = Name of the Schema file for the xml file mentioned above



Once you have provided the information, Test the connection.







Once the connection is successful, now select the data server thus created and create a new Physical server.








Now go to the designer tab and go to Model.
Create a new model





Select XML as technology and select the logical schema also.
Go to selective reverse engineering




And select the employee xml document you have created.
And reverse engineer the document.




Once it will be reverse engineered you can see an employee record in your model.
Now here lots of things are there to be note down.
As you can see with each element an order is also added which determine at what position the data should come.




As EmpId is the first element the order is 0. Similarly EmpName is the second element hence the order is 1.
Now this was easy, now we will try to see how we can incorporate complex xml document.
We will change the existing document to take multiple entries for employees
Load Complex XML data in to ODI

Let’s change the Schema first
<?xml version="1.0" encoding="windows-1252" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns="http://www.example.org"
targetNamespace="http://www.example.org"
elementFormDefault="qualified">
<xsd:element name="Employees">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Employee" maxOccurs="unbounded">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="EmpId" type="xsd:integer"/>
<xsd:element name="EmpName" type="xsd:string"/>
<xsd:element name="EmpSalary" type="xsd:integer"/>
<xsd:element name="EmpLocation" type="xsd:string"/>
<xsd:element name="EmpDepartment" type="xsd:string"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>

AS you can see a new element is added with a complex type for multiple occurrence of the element.
Now create an xml document for the same.
<?xml version="1.0" encoding="windows-1252" ?>
<Employees>
<Employee>
<EmpId>325856</EmpId>
<EmpName>Arpit</EmpName>
<EmpSalary>1231231</EmpSalary>
<EmpLocation>Bangalore</EmpLocation>
<EmpDepartment>Oracle</EmpDepartment>
</Employee>
<Employee>
<EmpId>3258</EmpId>
<EmpName>Raj</EmpName>
<EmpSalary>231231</EmpSalary>
<EmpLocation>Bangalore</EmpLocation>
<EmpDepartment>Oracle</EmpDepartment>
</Employee>
<Employee>
<EmpId>325823</EmpId>
<EmpName>Gaurav</EmpName>
<EmpSalary>1000090</EmpSalary>
<EmpLocation>Bangalore</EmpLocation>
<EmpDepartment>Deloitte</EmpDepartment>
</Employee>
</Employees>

As you can observer there is a root element Employees and then there is a complex element Employee which has a multiple occurrence. Follow the same steps that we have created for Simple and try to reverse engineer it.



You can observer that there are two tables which are getting created, one for the Employees and one for the Employee. So when you will reverse engineer you can find the following entry.
The files will be uploaded in the ODI in the exact hierarchy




Now if you will compare the data in the two tables
Employees




Employee



You can observer that the Employees and Employee table are linked to each other using a primary foreign key relationship. This is how the exact hierarchy is maintained in the ODI.
DB to XML

Create a sample XML and XSD file for the same
XML File
<?xml version="1.0" encoding="windows-1252" ?>
<Schemes>
<Scheme>
<Name>Test</Name>
<Id>123</Id>
<Location>Test</Location>
<Department>Test</Department>
</Scheme>
</Schemes>

XML Schema
<?xml version="1.0" encoding="windows-1252" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns="http://www.example.org"
targetNamespace="http://www.example.org"
elementFormDefault="qualified">
<xsd:element name="Schemes">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Scheme">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Name" type="xsd:string"/>
<xsd:element name="Id" type="xsd:integer"/>
<xsd:element name="Location" type="xsd:string"/>
<xsd:element name="Department" type="xsd:string"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
This will be the target for our use case
Let’s create a physical architecture and a model for the same
Create a data server




And a new physical schema



Create a model



And reverse engineer the tables



Now we have created the target model, now we need to create a source model.
For this purpose we have created a table and have populated some record in the same




Create a data model for the same



Now we will create an interface to load the data from DB to XML
And take the database element as source and XML element as target and do a mapping




Import the following knowledge module





Go to the flow tab of interface and make sure the LKM is selected to load the data from source to the Target



Now select the target and choose the IKM that you have imported. Here you need to make two changes.
The first is to make the CREATE_XML_FILE to true and the second is XML_PATH to the location and file name where you wanted to write your file.
Make TRUNCATE_SCHEMA to true and then Provide the root table name as Schemes



Now you are done with the interface. Execute the interface to find out the result



Now once it is completed successfully. Go the location which you have specified in the XML_PATH and review if the file is created and is in the correct structure.
So a file is created with the following entry
<?xml version="1.0" encoding="UTF-8"?>
<Schemes>
<Scheme>
<Name>arpit</Name>
<Id>123456</Id>
<Location>Con</Location>
<Department>OPT</Department>
</Scheme>
<Scheme>
<Name>chandan</Name>
<Id>234567</Id>
<Location>Con</Location>
<Department>OPT</Department>
</Scheme>
<Scheme>
<Name>vasudha</Name>
<Id>345678</Id>
<Location>Con</Location>
<Department>OPT</Department>
</Scheme>
<Scheme>
<Name>hima</Name>
<Id>456789</Id>
<Location>Con</Location>
<Department>OPT</Department>
</Scheme>
<Scheme>
<Name>gaurav</Name>
<Id>612345</Id>
<Location>Con</Location>
<Department>OPT</Department>
</Scheme>
<Scheme>
<Name>ravindra</Name>
<Id>712345</Id>
<Location>BTA</Location>
<Department>OPT</Department>
</Scheme>
</Schemes>

As you can observe the XML has populated all the records from database and it is following the exact order what we have specified for different element in the ODI Mapping.