I'm using Oracle version - 11.2.0.4
I've reproduced the problem I'm facing in a more simplified manner below.
I've 2 tables with XMLTYPE column.
Table 1 (base_table in my example below) has storage model for XMLTYPE as BINARY XML.
Table 2 (my_tab) has storage model for XMLTYPE as CLOB.
With the XML in base_table I'm extracting the value of an attribute based on certain condition. This attribute in turn is a name of a node in xml contained in my_tab and I want to extract that node's value from my_tab.
Please note that I do not have the liberty to change this logic at the moment.
Code was working fine till the storage model for XMLTYPE column was CLOB in both tables. Recently base_table was recreated (drop and create), so it's storage model got modified as BINARY XML as I understand that's the default storage model in version 11.2.0.4
Here is create table stmt -
create table base_table(xml xmltype);
create table my_tab(xml xmltype)
xmltype column "XML" store as clob;
and sample data -
insert into base_table(xml)
values (xmltype('<ROOT>
<ELEMENT NAME="NODEA">
<NODE1>A-Node1</NODE1>
<NODE2>A-Node2</NODE2>
</ELEMENT>
<ELEMENT NAME="NODEB">
<NODE1>B-Node1</NODE1>
<NODE2>B-Node2</NODE2>
</ELEMENT>
<ELEMENT NAME="NODEC">
<NODE1>C-Node1</NODE1>
<NODE2>C-Node2</NODE2>
</ELEMENT>
</ROOT>')
);
insert into my_tab(xml)
values (xmltype('<TEST_XML>
<SOME_NODE>
<XYZ>
<NODEB>My area of concern</NODEB>
<OTHER_NODE> Something irrelevant </OTHER_NODE>
</XYZ>
</SOME_NODE>
<SOME_OTHER_NODE>
<ABC> Some value for this node </ABC>
</SOME_OTHER_NODE>
</TEST_XML>')
);
This query fails with error:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00601: Invalid token in: '///text()'
select extract(t.xml, sd.tag_name).getstringval()
from (select '//' || extract(value(d), '//@NAME').getstringval() || '/text()' as tag_name
from base_table b,
table(xmlsequence(extract(b.xml, '//ROOT/ELEMENT'))) d
where extract(value(d), '//NODE2/text()').getstringval() = 'B-Node2') sd,
my_tab t;
Below query works fine and is able to extract the value of node I'm interested in. It can be seen that tag_name is fetched as required, but when it is used within "extract", it's value is somehow lost.
select sd.tag_name, extract(t.xml, '//NodeB/text()').getstringval()
from (select '//' || extract(value(d), '//@NAME').getstringval() || '/text()' as tag_name
from base_table b,
table(xmlsequence(extract(b.xml, '//ROOT/ELEMENT'))) d
where extract(value(d), '//NODE2/text()').getstringval() = 'B-Node2') sd,
my_tab t;
*****************************************************************
If I change XMLTYP storage model of base_table back to CLOB, the erroneous query works fine again.
I would like to understand what's going wrong with storage model as BINARY XML.
Thanks,
Kailash
My guess is that its an optimizer issue. For example, if we tweak the query to force a certain order of operations, it appears to be ok.
SQL> drop table base_table purge;
Table dropped.
SQL> drop table my_tab purge;
Table dropped.
SQL>
SQL> create table base_table(xml xmltype);
Table created.
SQL>
SQL> create table my_tab(xml xmltype)
2 xmltype column "XML" store as clob;
Table created.
SQL>
SQL> insert into base_table(xml)
2 values (xmltype('<ROOT>
3 <ELEMENT NAME="NODEA">
4 <NODE1>A-Node1</NODE1>
5 <NODE2>A-Node2</NODE2>
6 </ELEMENT>
7 <ELEMENT NAME="NODEB">
8 <NODE1>B-Node1</NODE1>
9 <NODE2>B-Node2</NODE2>
10 </ELEMENT>
11 <ELEMENT NAME="NODEC">
12 <NODE1>C-Node1</NODE1>
13 <NODE2>C-Node2</NODE2>
14 </ELEMENT>
15 </ROOT>')
16 );
1 row created.
SQL>
SQL> insert into my_tab(xml)
2 values (
3 xmltype('<TEST_XML>
4 <SOME_NODE>
5 <XYZ>
6 <NODEB>My area of concern</NODEB>
7 <OTHER_NODE> Something irrelevant </OTHER_NODE>
8 </XYZ>
9 </SOME_NODE>
10 <SOME_OTHER_NODE>
11 <ABC> Some value for this node </ABC>
12 </SOME_OTHER_NODE>
13 </TEST_XML>')
14 );
1 row created.
SQL>
SQL> select sd.tag_name, extract(t.xml, sd.tag_name).getstringval()
2 from (select '//' || extract(value(d), '//@NAME').getstringval() || '/text()' as tag_name
3 from base_table b,
4 table(xmlsequence(extract(b.xml, '//ROOT/ELEMENT'))) d
5 where extract(value(d), '//NODE2/text()').getstringval() = 'B-Node2') sd,
6 my_tab t;
select sd.tag_name, extract(t.xml, sd.tag_name).getstringval()
*
ERROR at line 1:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00601: Invalid token in: '///text()'
SQL>
SQL>
SQL> with sd as
2 (select /*+ no_merge materialize */ '//' || extract(value(d), '//@NAME').getstringval() || '/text()' as tag_name
3 from base_table b,
4 table(xmlsequence(extract(b.xml, '//ROOT/ELEMENT'))) d
5 where extract(value(d), '//NODE2/text()').getstringval() = 'B-Node2')
6 select sd.tag_name, extract(t.xml, sd.tag_name).getstringval()
7 from sd,
8 my_tab t;
TAG_NAME
----------------------------------------------------------------------------------------------------
EXTRACT(T.XML,SD.TAG_NAME).GETSTRINGVAL()
----------------------------------------------------------------------------------------------------
//NODEB/text()
My area of concern
SQL>
SQL>
I would log a support call via support.oracle.com. (My test above done on 12.1.0.2 btw)
Hope this helps.