Tutorial #60: Load data from Oracle table to XML using OdiSqlUnload

Hi everyone!

It’s time for yet another ODI tutorial. Through this post, we will learn how to export the data from an Oracle table to an XML file. To implement this task we would be using the built in tool called OdiSqlUnload that comes shipped with Oracle Data Integrator.

Pre-requisites: Oracle Data Integrator 11g (build version 11.1.1.7.0)

Step 1: Create table in Oracle

Create a new table called Department in your work repository and insert a few dummy values!

create table Department (DeptId int primary key, DeptName varchar(20));

insert into Department values(201, 'Computers');
insert into Department values(202,'Electronics');
insert into Department values(203,'Mech');

Step 2: Create XML file

Make a new blank XML file called sample.xml. Do not add any contents to it as it would be done using a built in tool. 🙂

Step 3: Create Package and use OdiSqlUnload

Open ODI Studio and connect to your work repository. Create a new package named table_to_xml in any of your existing projects!

Now, drag and drop the OdiSqlUnload tool under the Files section.

create_package_drag_tool

Add the required details and finally save and run the package. Check the status of the operation under the Operator tab. If the operation is successful you should see the XML file now populated with data from your Oracle table.

add_required_fields

output

Output

<?xml version="1.0" encoding="ISO-8859-1"?>

<!-- File generated at 16/10/13 8:03 PM -->

<table>
    <row>
      <DEPTID><![CDATA[201]]></DEPTID>
      <DEPTNAME><![CDATA[Computers]]></DEPTNAME>
    </row>
    <row>
      <DEPTID><![CDATA[202]]></DEPTID>
      <DEPTNAME><![CDATA[Electronics]]></DEPTNAME>
    </row>
    <row>
      <DEPTID><![CDATA[203]]></DEPTID>
      <DEPTNAME><![CDATA[Mech]]></DEPTNAME>
    </row>
</table>

Tutorial #31: Load data from XML file to Oracle table using ODI

Hey everyone!

In this tutorial, I will be explaining how to populate an existing Oracle database table from an XML file using Oracle Data Integrator. Initially, you need to create a well-formed XML file that will contain your data. Here is what my XML file looks like:

<Employee>
<Id>101</Id>
<Name>John</Name>
<City>Mumbai</City>
<Salary>2000</Salary>
</Employee>

Note: Make sure your XML is well-formed and contains no spaces between tags or else the data won’t get reflected in the target table.

Pre-requisites: Oracle 10g Express Edition with *SQLPlus, Oracle Data Integrator 11g (build version 11.1.1.7.0)

Once you have created your XML file you now need to create your target table. Open *SQLPlus, connect to your schema and write the following SQL command.

create table XML_TEST(EmpId int primary key, EmpName varchar(30), City varchar(30), Salary float);

Now open ODI Studio and carry out the following steps!

Step 1: Create new data server, physical schema for XML file

xml_data_server_1

xml_data_server_2

xml_physical_schema

While creating a new data server make sure you click on the Test Connection button in order to verify that the connection is successful.

Step 2: Create logical schema for XML file

xml_logical_schema


create logical schema for XML

Step 3: Create new Model folder and reverse engineer XML file

model_1

model_2

model_3

model_4

model_5

Repeat steps 1 to 3 for your Oracle table XML_TEST as well.

Step 4: Create new interface

Create new Project named xml_test_project. Expand First Folder –> Interface and right click to create new Interface.

interface_1

Perform mapping between source XML file and target table.

interface_2

Select LKM as SQL to Oracle, IKM as Oracle Incremental Update and CKM Oracle.

interface_3

Finally run the interface. Check the status of the operation under the Operator tab. If the process was completed successfully you can now see the XML data inside your target table.

final_output

Output

Output

Tutorial #14: Reading XML file in Java

Hello everyone,

Today’s tutorial talks about reading an XML file in Java. In situations where you need to build enterprise Java applications, XML files are used for data representation, messaging and transfer. Java provides good support for handling XML files/documents by using various XML parsers available.

Now, there are various ways to read an XML file in Java. Some of them are as follows:

1. JAXP – Java API for XML parsing

Java provides support for reading/writing XML file and accessing any element from XML file. All XML parsing related classes and methods are present inside JAXP. All XML parsers are in javax.xml.parsers package.

2. Parse XML file using SAX parser

SAX parser is faster and uses less memory than DOM parser (discussed below). The Simple API for XML (SAX) provides a mechanism to read data from an XML document by operating on each piece of the XML document sequentially.

Program to implement SAX parser

Step 1: Create a new Java Application Project

Step 2:

The SAX parser uses a callback function org.xml.sax.helpers.DefaultHandler and hence we need to create a class say MyHandler that extends the DefaultHandler class.

More information can be obtained from here.

MyHandler.java


package pack;

import org.xml.sax.Attributes;
import org.xml.sax.SAXException;
import org.xml.sax.helpers.DefaultHandler;

public class MyHandler extends DefaultHandler {

	@Override
	public void startDocument() throws SAXException {
		System.out.println("Start of document...");
	}

	@Override
	public void endDocument() throws SAXException {
		System.out.println("End of document");	
	}

	@Override
	public void startElement(String uri, String localName, String qName,
			Attributes attributes) throws SAXException {
		System.out.println("Got new element : "+qName);
	}

	@Override
	public void endElement(String uri, String localName, String qName)
			throws SAXException {
		System.out.println("End of element");
	}
	
}

Step 3: Finally, implement the main method!

App1.java

package pack;

import org.xml.sax.*;
import javax.xml.parsers.*;

public class App1 {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		
		
		try{
			SAXParser parser = SAXParserFactory.newInstance().newSAXParser();	
		 //sample.xml is an XML file you want to parse. include it inside your source folder
			parser.parse("src/sample.xml", new MyHandler());
			System.out.println("Your XML file is valid!");
			
		}catch(SAXException ex){
			System.out.println("Validation failed!");
			System.out.println(ex.getMessage());
		}catch(Exception ex){
			System.out.println("Cannot read xml file");
			ex.printStackTrace();
		}

	}

}

sample.xml

<?xml version="1.0" encoding="UTF-8"?>
<EmpData>
<Emp id="101" name="karan" designation="developer"/>
<Emp id="102" name="kamal" designation="developer"/>
<Emp id="103" name="ganesh" designation="developer"/>
</EmpData>

Output
Start of document…
Got new element : EmpData
Got new element : Emp
End of element
Got new element : Emp
End of element
Got new element : Emp
End of element
End of element
End of document
Your XML file is valid!

Program to implement DOM parser

DOM parser will parse the entire XML document and then load it into the memory. It creates a tree structure for traversal.

AppMain2.java

package pack;

import java.io.IOException;

import javax.xml.parsers.*;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.SAXException;


public class AppMain2 {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
			
			try {
				DocumentBuilderFactory fact = DocumentBuilderFactory.newInstance();
				DocumentBuilder db=	fact.newDocumentBuilder();
				Document d = db.parse("src/sample.xml");
				System.out.println("Validation is over");
				System.out.println("Root: "+d.getDocumentElement().getTagName());
				
				
				NodeList nList = d.getElementsByTagName("Emp");
				 
			 
				for (int temp = 0; temp < nList.getLength(); temp++) {
			 
					Node nNode = nList.item(temp);
			 
					
					if (nNode.getNodeType() == Node.ELEMENT_NODE) {
			 
						Element eElement = (Element) nNode;
			 
						System.out.println("Emp Id : " + eElement.getAttribute("id"));
						System.out.println("Employee Name : " + eElement.getAttribute("name"));
						System.out.println("Employee designation: " + eElement.getAttribute("designation"));
						
					}
				}
				
				
			} catch (ParserConfigurationException e) {
				System.out.println("Unable to initialize DOM Parser");
				e.printStackTrace();
			} catch (SAXException e) {
				System.out.println("Error in XML document");
				e.printStackTrace();
			} catch (IOException e) {
					System.out.println("Unable to read file");
					e.printStackTrace();
			}
			

	}

}

Output
Validation is over
Root: EmpData
Emp Id : 101
Employee Name : karan
Employee designation: developer
Emp Id : 102
Employee Name : kamal
Employee designation: developer
Emp Id : 103
Employee Name : ganesh
Employee designation: developer

So that’s all about reading XML files in Java. There are several other ways which are better than the ones discussed over here. I will try to include them in my further tutorials! Enjoy! 🙂

Tutorial #13: Struts 2 XML based validation framework

Hello everyone,

Here’s another tutorial on Struts 2 that talks about the use of the validation framework that help enable validation rules to your actions before they are executed. The validation framework handles both server side and client side form validation. The main advantage of the validation framework is that it helps separate the validation logic from your actual Java code.

So let’s start implementing our validation logic by creating a new Struts 2 application!

Requirements: Eclipse for Java EE(latest version preferably), Apache Tomcat Server, Struts 2 libraries

Step 1: Create a new Dynamic Web Project in Eclipse and name it StrutsValidation.

Step 2: Create a new Action class named RegisterAction.java and add the following code:

package actions;

import com.opensymphony.xwork2.ActionSupport;

public class RegisterAction extends ActionSupport
{
	
	private static final long serialVersionUID = 1L;
	
	private String name;
	private int age;
	private String email;
	private String contact;
	
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	
	public int getAge() {
		return age;
	}
	public void setAge(int age) {
		this.age = age;
	}
	public String getContact() {
		return contact;
	}
	public void setContact(String contact) {
		this.contact = contact;
	}
	
	
	public String execute() throws Exception
	{
		return SUCCESS;
	}
	
}

Step 3: After you have created your Action class, you need to create the corresponding JSP page that will display the Registration form.

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<%@ taglib prefix="s" uri="/struts-tags" %>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Registration form</title>
</head>
<body>

 <s:form action="register.action" method="post" validate="true">
      <s:textfield name="name" label="name" size="20" />
      <s:textfield name="age" label="age" size="20" />
      <s:textfield name="email" label="email"/>
      <s:textfield name="contact" label="contact"/>
     <s:submit name="submit" label="submit" align="center" />
 </s:form>

</body>
</html>

In order to add client side validation we just need to add validate=”true” in our JSP file (as done above) and Struts 2 will automatically generate client side validation code of form.

Step 4: It’s now time to add your validation logic. To do so, we create an XML file and name it RegisterAction-validation.xml. Here it is important to note that the name of the XML file should be in the form (Action-class-name)-validation.xml

<?xml version="1.0" encoding="UTF-8"?>
 <!DOCTYPE validators PUBLIC
  		"-//Apache Struts//XWork Validator 1.0.3//EN"
  		"http://struts.apache.org/dtds/xwork-validator-1.0.3.dtd">

<validators>

   <field name="name">
      <field-validator type="requiredstring">
         <message>
            The name is required!
         </message>
      </field-validator>
   </field>

   <field name="age">
     <field-validator type="int">
         <param name="min">29</param>
         <param name="max">64</param>
         <message>
            Age must be in between 28 and 65!
         </message>
      </field-validator>
   </field>
   
   
   <field name="email">
  		<field-validator type="requiredstring">
  		<message>email is required!</message>
  		</field-validator>
  		<field-validator type="email">
  		<message>invalid email id!</message>
  		</field-validator>
  	</field>
  
   <field name="contact">
        <field-validator type="requiredstring">
            <message>Contact no is required! </message>
        </field-validator>
        <field-validator type="stringlength">
        <param name="minLength">10</param>
        <param name="maxLength">15</param>
        <message>Your contact no needs to be between ${minLength} and ${maxLength} digits long</message>
        </field-validator>
    </field>
   
</validators>

It is important to note that the above XML file must be inside the same package as that of your Action class. The XML file contains different field validators for respective fields of the form.

Step 5: Finally, ensure that no errors are present and run the application on Tomcat Server.

struts.xml

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE struts PUBLIC
	"-//Apache Software Foundation//DTD Struts Configuration 2.1//EN"
	"http://struts.apache.org/dtds/struts-2.1.dtd">
	
<struts>
	<package name="p1" namespace="" extends="struts-default">
	  
	   
	  <action name="register" class="actions.RegisterAction">
	  		<result name="success">success.jsp</result>
	  		<result name="input">register.jsp</result>
	  </action>
	      
	</package>
</struts>

web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" id="WebApp_ID" version="2.5">
  <display-name>StrutsValidation</display-name>
  <welcome-file-list>
    <welcome-file>index.html</welcome-file>
    <welcome-file>index.htm</welcome-file>
    <welcome-file>index.jsp</welcome-file>
    <welcome-file>default.html</welcome-file>
    <welcome-file>default.htm</welcome-file>
    <welcome-file>default.jsp</welcome-file>
  </welcome-file-list>
   <filter>
    <filter-name>struts2</filter-name>
    <filter-class>org.apache.struts2.dispatcher.FilterDispatcher</filter-class>
  </filter>
  <filter-mapping>
    <filter-name>struts2</filter-name>
    <url-pattern>/*</url-pattern>
  </filter-mapping>
</web-app>
Home Page

Home Page

Registration form showing validation

Registration form showing validation

Successful registration

Successful registration

Source code of this tutorial can be found over here.