PL/SQL: Extract XML Data using SQL

Few examples to extract xml data in a SQL query.

#1. Extract Master Child xml using xmltable
XMLTABLE

 select header_row.department_id
       ,header_row.department_name  
       ,child_row.employee_id  
       ,child_row.first_name
       ,child_row.last_name  
 from XMLTABLE(
               XMLNAMESPACES(default 'http://johnytips.blogspot.com.au/ns/department')  
              ,'/department'  
          PASSING xmltype(  
              '<department xmlns="http://johnytips.blogspot.com.au/ns/department" id="1">  
                    <department_name>Research</department_name>  
                    <employees>  
                         <employee id="1">  
                              <first_name>ANOOP</first_name>  
                              <last_name>JOHNY</last_name>  
                         </employee>  
                         <employee id="2">  
                              <first_name>ANISH</first_name>  
                              <last_name>JOHNY</last_name>  
                         </employee>  
                    </employees>  
               </department>')  
          COLUMNS   
               department_id   VARCHAR2(30) PATH '@id',  
               department_name VARCHAR2(10) PATH 'department_name',  
               child_rows XMLTYPE PATH 'employees'  
              ) header_row  
     ,XMLTABLE(
               XMLNAMESPACES(default 'http://johnytips.blogspot.com.au/ns/department')  
              ,'/employees/employee'  
          PASSING header_row.child_rows  
          COLUMNS  
               employee_id NUMBER PATH '@id',   
               first_name  VARCHAR2(30) PATH 'first_name',  
               last_name   VARCHAR2(30) PATH 'last_name'
              ) child_row;  

#2. EXTRACTVALUE

 with t as  
  (select   
          xmltype('<employee>  
                        <employee_id>1</employee_id>  
                        <first_name>ANOOP</first_name>  
                        <last_name>JOHNY</last_name>  
                   </employee>') str   
   from dual)  
 select extractvalue(str,'/employee/employee_id') EMPLOYEE_ID  
       ,extractvalue(str,'/employee/first_name')  FIRST_NAME  
       ,extractvalue(str,'/employee/last_name')   LAST_NAME  
 from t;  

#3.EXTRACT

 with t as  
  (select   
          xmltype('<employee>  
                        <employee_id>1</employee_id>  
                        <first_name>ANOOP</first_name>  
                        <last_name>JOHNY</last_name>  
                   </employee>') str   
   from dual)  
 select extract(str,'/employee/employee_id/text()') EMPLOYEE_ID  
       ,extract(str,'/employee/first_name/text()')  FIRST_NAME  
       ,extract(str,'/employee/last_name/text()')   LAST_NAME  
 from t;  

#4. To extract with the xml tag

 with t as  
  (select   
          xmltype('<employee>  
                        <employee_id>1</employee_id>  
                        <first_name>ANOOP</first_name>  
                        <last_name>JOHNY</last_name>  
                   </employee>') str   
   from dual)  
 select extract(str,'/employee/employee_id') EMPLOYEE_ID  
       ,extract(str,'/employee/first_name')  FIRST_NAME  
       ,extract(str,'/employee/last_name')   LAST_NAME  
 from t;  

#5. Prior to Oracle Database 10g Release 2 using xmlsequence
XMLSEQUENCE

 select extractvalue(column_value, '/employee/first_name') "FIRST_NAME"  
       ,extractvalue(column_value, '/employee/last_name')  "LAST_NAME"  
 from table(xmlsequence(xmltype('<employees>  
                                      <employee>  
                                           <employee_id>1</employee_id>  
                                           <first_name>ANOOP</first_name>  
                                           <last_name>JOHNY</last_name>  
                                      </employee>  
                                      <employee>   
                                           <employee_id>2</employee_id>  
                                           <first_name>ANISH</first_name>  
                                           <last_name>JOHNY</last_name>  
                                      </employee>  
                                 </employees>').extract('/employees/employee')));  

Feel free to point out if anything is missing/wrong in this blog