1  create table jitender_xml
  2               (id number
  3*         ,data xmltype)
SQL> /

Table created.

  1  insert into jitender_xml
  2  values(1,xmltype('<jitender_xml>
  3  <Employee empid="1111" type="admin">
  4  <first_name>john</first_name>
  5  <last_name> cena</last_name>
  6  <age> 30 </age>
  7  <email_id> abc@gmail.com</email_id>
  8  </Employee>
  9* </jitender_xml>'))
SQL> /

1 row created.

  1  insert into jitender_xml
  2  values(2,xmltype('<jitender_xml>
  3  <Employee empid="2222" type="boss">
  4  <first_name>dwayne</first_name>
  5  <last_name> johnson</last_name>
  6  <age> 37 </age>
  7  <email_id> ddd@gmail.com</email_id>
  8  </Employee>
  9* </jitender_xml>'))
SQL> /

1 row created.

  1  insert into jitender_xml
  2  values(3,xmltype('<jitender_xml>
  3  <Employee empid="3333" type="manager">
  4  <first_name>steve</first_name>
  5  <last_name> austin</last_name>
  6  <age> 40 </age>
  7  <email_id> sa@gmail.com</email_id>
  8  </Employee>
  9  <Employee empid="4444" type="nri">
 10  <first_name>khali</first_name>
 11  <last_name> singh</last_name>
 12  <age> 35 </age>
 13  <email_id> ks@gmail.com</email_id>
 14  </Employee>
 15* </jitender_xml>'))
SQL> /

1 row created.

SQL> select * from jitender_xml
  2  /

        ID DATA
---------- -----------------------------------------------
         1 <jitender_xml>
           <Employee empid="1111" type="admin">
           <first_name>john</first_name>
           <last_name> cena</last_name>
           <age> 30 </age>
           <email_id> abc@gmail.com</email_id>
           </Employee>
           </jitender_xml>

         2 <jitender_xml>
           <Employee empid="2222" type="boss">
           <first_name>dwayne</first_name>
           <last_name> johnson</last_name>
           <age> 37 </age>
           <email_id> ddd@gmail.com</email_id>
           </Employee>
           </jitender_xml>

         3 <jitender_xml>
           <Employee empid="3333" type="manager">
           <first_name>steve</first_name>
           <last_name> austin</last_name>
           <age> 40 </age>
           <email_id> sa@gmail.com</email_id>
           </Employee>
           <Employee empid="4444" type="nri">
           <first_name>khali</first_name>
           <last_name> singh</last_name>
           <age> 35 </age>
           <email_id> ks@gmail.com</email_id>
           </Employee>
           </jitender_xml>

  1  SELECT j.id, x.*
  2       FROM jitender_xml j,
  3            XMLTABLE ('/jitender_xml/Employee'
  4                      PASSING j.data
  5                      COLUMNS firstname VARCHAR2(30) PATH 'first_name',
  6                              lastname VARCHAR2(30) PATH 'last_name') x
  7*     WHERE j.id = 1
SQL> /
                                                  *
ERROR at line 5:
ORA-01780: string literal required

SQL> SELECT j.id, x.*
  2  FROM jitender_xml j,
  3  XMLTABLE ('/jitender_xml/Employee'
  4  PASSING j.data
  5  COLUMNS firstname VARCHAR2(30) PATH 'first_name',
  6  lastname VARCHAR2(30) PATH 'last_name') x
  7  WHERE j.id = 1
  8  /
        ID FIRSTNAME                      LASTNAME
---------- ------------------------------ ------------------------------
         1 john                            cena

1 row selected.

SQL> @v

Oracle version: 11.2.0.4.0

SQL> SELECT j.id, x.*
  2  FROM jitender_xml j,
  3  XMLTABLE ('/jitender_xml/Employee'
  4  PASSING j.data
  5  COLUMNS firstname VARCHAR2(30) PATH 'first_name',
  6  lastname VARCHAR2(30) PATH 'last_name') x
  7  WHERE j.id = 1
  8  /
        ID FIRSTNAME                      LASTNAME
---------- ------------------------------ ------------------------------
         1 john                            cena

1 row selected.

SQL> @v

Oracle version: 10.2.0.4.0