Invalid/Inconsistent Results and/or an ORA-19025 from EXTRACTVALUE and XQUERY with Binary XML Storage (Doc ID 1431465.1)

Last updated on FEBRUARY 08, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.2 and later
Information in this document applies to any platform.

Symptoms

Given the table TC created as follows:

create table TC (
id number
, binary_xml xmltype
, clob_xml xmltype
)
xmltype column binary_xml store as securefile binary xml
xmltype column clob_xml store as securefile clob;


With the following rows:

ID BINARY_XML CLOB_XML
---- ----------------------------------------
----------------------------------------
101 <ROOT>T1</ROOT> <ROOT>T1</ROOT>
201 <ROOT>T2 before<EMPTY/>after</ROOT> <ROOT>T2
before<EMPTY/>after</ROOT>
301 <ROOT> <ROOT><A>a</A></ROOT>
<A>a</A>
</ROOT>
401 <ROOT> <ROOT><A>a</A><B>b</B></ROOT>
<A>a</A>
<B>b</B>
</ROOT>



We get invalid/inconsistent results and/or an ORA-19025 if extractValue is
called once versus if it is called twice for the same XPATH. This issue is
not seen where XML is stored as CLOB.


SQL> -- We get NULL with CLOB_XML where ID = 301
SQL> select id,
2 extractValue(clob_xml, '/*/text()') "CLOB_XML",
3 extractValue(clob_xml, '/*/text()') "CLOB_XML"
4 from tc where id = 301;

ID CLOB_XML CLOB_XML
---- --------------- ---------------
301



1 row selected.

SQL> -- We get "a" with BINARY_XML for the same row where id = 301
SQL> select id,
2 extractValue(BINARY_XML, '/*/text()') "BINARY_XML",
3 extractValue(BINARY_XML, '/*/text()') "BINARY_XML"
4 from tc
5 where id = 301;

ID BINARY_XML BINARY_XML
---- --------------- ---------------
301 a a



1 row selected.


SQL> -- Now do the same test where id = 401
SQL> -- We get NULL with CLOB_XML
SQL> select id,
2 extractValue(clob_xml, '/*/text()') "CLOB_XML",
3 extractValue(clob_xml, '/*/text()') "CLOB_XML"
4 from tc where id = 401;

ID CLOB_XML CLOB_XML
---- --------------- ---------------
401



1 row selected.


SQL> -- But we get ORA-19025 with BINARY_XML
SQL> select id,
2 extractValue(BINARY_XML, '/*/text()') "BINARY_XML",
3 extractValue(BINARY_XML, '/*/text()') "BINARY_XML"
4 from tc
5 where id = 401;
select id,
*
ERROR at line 1:
ORA-19025: EXTRACTVALUE returns value of only one node



We see the same inconsistent/invalid results when calling XMLCAST/XMLQUERY
for the same XPATH once versus calling it twice:


SQL> -- We get "a" and "ab" for BINARY_XML where id = 301, 401 respectively
SQL> select id,
2 xmlcast(xmlquery('$r/ROOT/text()'
3 passing BINARY_XML as "r" RETURNING CONTENT) as
varchar2(15)) should_be_blank
4 from tc;

ID SHOULD_BE_BLANK
---- ---------------
101 T1
201 T2 beforeafter
301 a
401 ab



4 rows selected.


SQL> -- Calling XMLCAST/XMLQUERY twice for the same XPATH /ROOT with
BINARY_XML, we then get NULLs for row = 301, 401
SQL> select id,
2 -- call it twice and correct answer
3 xmlcast(xmlquery('$r/ROOT/text()' passing BINARY_XML as "r" RETURNING
CONTENT) as varchar2(15)) should_be_blank
4 , xmlcast(xmlquery('$r/ROOT/text()' passing BINARY_XML as "r" RETURNING
CONTENT) as varchar2(15)) should_be_blank_too
5 , xmlcast(xmlquery('$r/ROOT/*/text()' passing BINARY_XML as "r"
RETURNING CONTENT) as varchar2(15)) both_text_elements
6 from tc;

ID SHOULD_BE_BLANK SHOULD_BE_BLANK BOTH_TEXT_ELEME
---- --------------- --------------- ---------------
101 T1 T1
201 T2 beforeafter T2 beforeafter
301 a
401 ab



4 rows selected.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms