Using XMLTABLE WHen I execute in sql*PLUS
[CODE]
SQL> WITH warehouses AS 
  2   (SELECT XMLTYPE('
  3  <ArrayOfAnyType xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  4    <anyType xsi:type="Data">
  5      <name>John</name>
  6      <id>xxxx</id>
  7      <No>123</No>
  8      <Division />
  9      <Days>0</Days>
 10      <PTO_Days>0</PTO_Days>
 11      <Transfer_Date />
 12      <CURRENT_LOCATION>US</CURRENT_LOCATION>
 13    </anyType>
 14    <anyType xsi:type="Data">
 15      <name>Jim</name>
 16      <id>PPPP</id>
 17      <No>900</No>
 18      <Division />
 19      <Days>0</Days>
 20      <PTO_Days>0</PTO_Days>
 21      <Transfer_Date />
 22      <CURRENT_LOCATION>UK</CURRENT_LOCATION>
 23    </anyType>
 24  </ArrayOfAnyType>') warehouse_spec FROM dual)
 25  SELECT p."uid", p."uname"
 26    FROM warehouses w,
 27         XMLTABLE (
 28           '/ArrayOfAnyType/anyType'
 29           PASSING w.warehouse_spec
 30           COLUMNS "uname" VARCHAR2 (100) PATH '//name',
 31                   "uid"   VARCHAR2 (100) PATH '//id') p
 32  /
    <Division />
               *
ERROR at line 8:
ORA-29900: operator binding does not exist
ORA-06540: PL/SQL: compilation error
ORA-06553: PLS-907: cannot load library unit SYS.XQSEQUENCEFROMXMLTYPE
(referenced by SYS.XQSEQUENCE)
[/code]
=====================================================================================
Second Approach 
[code]
SQL> With data as ( select xmltype(
  2  '<ArrayOfAnyType xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
  3   xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://tempuri.org/">
  4   <anyType xsi:type="Data">
  5   <name>John</name>
  6   <id>xxxx</id>
  7   <No>123</No>
  8   <Division />
  9   <Days>0</Days>
 10   <PTO_Days>0</PTO_Days>
 11   <Transfer_Date />
 12   <CURRENT_LOCATION>US</CURRENT_LOCATION>
 13   </anyType>
 14   <anyType xsi:type="Data">
 15   <name>Jim</name>
 16   <id>PPPP</id>
 17   <No>900</No>
 18   <Division />
 19   <Days>0</Days>
 20   <PTO_Days>0</PTO_Days>
 21   <Transfer_Date />
 22   <CURRENT_LOCATION>UK</CURRENT_LOCATION>
 23   </anyType>
 24   </ArrayOfAnyType>') val from dual )
 25    select extractValue(value(x), '//name',
 26                'xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
 27                  xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://tempuri.org/"') 
 28            name,
 29          extractValue(value(x), '//id',
 30              'xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
 31                   xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://tempuri.org/"') 
 32            id
 33    from data, table(xmlsequence(extract(val, '//anyType',
 34                   'xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
 35                    xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://tempuri.org/"'))) 
x
 36   /
  from data, table(xmlsequence(extract(val, '//anyType',
                   *
ERROR at line 33:
ORA-29900: operator binding does not exist
ORA-06540: PL/SQL: compilation error
ORA-06553: PLS-907: cannot load library unit SYS.XMLSEQUENCEFROMXMLTYPE
(referenced by SYS.XMLSEQUENCE)
[/code]