SELECT XMLTYPE('
<XDATA>
  <Department deptno="100">
    <DeptName>Sports</DeptName>
    <EmployeeList>
      <Employee empno="200">
        <Ename>John</Ename>
        <Salary>33333</Salary>
      </Employee>
      <Employee empno="300">
        <Ename>Jack</Ename>
        <Salary>333444</Salary>
      </Employee>
    </EmployeeList>
  </Department>
  <Department deptno="200">
    <DeptName>Sports</DeptName>
    <EmployeeList>
      <Employee empno="400">
        <Ename>Marlin</Ename>
        <Salary>20000</Salary>
      </Employee>
    </EmployeeList>
  </Department>
</XDATA>') FROM dual;

SQL> CREATE TABLE mytable( the_xml clob)
  2  /

Table created.

SQL> 
SQL> INSERT INTO mytable values ('<CONTRACT>
  2  <CON_NUMBER>1</CON_NUMBER>
  3  <PRI_DATE>30-NOV-07</PRI_DATE>
  4  <END_DATE>20-NOV-08</END_DATE>
  5  <START_DATE>30-NOV-07</START_DATE>
  6  <PROD_CHARGE>0</PROD_CHARGE>
  7  <ANNUAL_CHARGE>0</ANNUAL_CHARGE>
  8  <PRODUCT>
  9  <PRODUCT_NO>2</PRODUCT_NO>
 10  <DATE_ADDED>30-NOV-07</DATE_ADDED>
 11  <PRIC_DATE>30-NOV-07</PRIC_DATE>
 12  <PRICE>20</PRICE>
 13  <DISCOUNT>10</DISCOUNT>
 14  <CON_NUMBER>1</CON_NUMBER>
 15  <APPLIANCE>
 16  <PRODUCT_NO>2</PRODUCT_NO>
 17  <APP_NO>3</APP_NO>
 18  <APP_NAME>HEATER</APP_NAME>
 19  <PRODUCT_NO>2</PRODUCT_NO>
 20  <APP_NO>2</APP_NO>
 21  <APP_NAME>FAN</APP_NAME>
 22  </APPLIANCE>
 23  </PRODUCT>
 24  <PRODUCT>
 25  <PRODUCT_NO>1</PRODUCT_NO>
 26  <DATE_ADDED>30-NOV-07</DATE_ADDED>
 27  <PRIC_DATE>30-NOV-07</PRIC_DATE>
 28  <PRICE>30</PRICE>
 29  <DISCOUNT>40</DISCOUNT>
 30  <CON_NUMBER>1</CON_NUMBER>
 31  <APPLIANCE>
 32  <PRODUCT_NO>1</PRODUCT_NO>
 33  <APP_NO>1</APP_NO>
 34  <APP_NAME>LIGHT</APP_NAME>
 35  </APPLIANCE>
 36  </PRODUCT>
 37  </CONTRACT>');

1 row created.

SQL> Create table mytable2 of xmltype; 

Table created.

SQL> insert into mytable2 select xmltype(the_xml) from mytable;

1 row created.

SELECT extractvalue(column_value,'//PRODUCT/PRODUCT_NO') pno, 
       extractvalue(column_value,'//PRODUCT/DISCOUNT')   dis
  FROM mytable, TABLE(XMLSequence(extract(XMLTYPE(the_xml),'//PRODUCT')));

pno     dis
--------------
2	10
1	40