Using XMLTABLE WHen I execute in sql*PLUS
[CODE]
SQL> WITH warehouses AS
2 (SELECT XMLTYPE('
3
4
5 John
6 xxxx
7 123
8
9 0
10 0
11
12 US
13
14
15 Jim
16 PPPP
17 900
18
19 0
20 0
21
22 UK
23
24 ') 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 /
*
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 '
4
5 John
6 xxxx
7 123
8
9 0
10 0
11
12 US
13
14
15 Jim
16 PPPP
17 900
18
19 0
20 0
21
22 UK
23
24 ') 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]