Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Extractvalue, namespace and null

Helio GeminianoFeb 17 2024 — edited Feb 18 2024

Using Oracle 19c, I created a table like below.

create table txml
( id number (10), xml XMLTYPE);
insert into txml values (1,'<a xmlns="www.xxx.com"><b>String1</b></a>');
insert into txml values (2,'<a xmlns="www.xxx.com"><b>String2</b></a>');
insert into txml values (3,'<a xmlns="www.xxx.com"><b>String3</b></a>');
insert into txml values (4,'<a><b>String4</b></a>');
insert into txml values (5,'<a><b>String5</b></a>');
insert into txml values (6,'<a><b>String6</b></a>');
commit;

The SELECT commands below work without errors.

select extractvalue(xml,'/a/b','xmlns="www.xxx.com"') b_value from txml;

B_VALUE             
--------------------
String1
String2
String3
<<NULL>>
<<NULL>>
<<NULL>>


select extractvalue(xml,'/a/b',null) b_value from txml;

B_VALUE             
--------------------
<<NULL>>
<<NULL>>
<<NULL>>
String4
String5
String6

Note that in the first SELECT the namespace is specified and only the values ​​of lines with ID 1,2,3 are returned. This is right.

In the second SELECT the namespace is "null" and only the values ​​of rows with ID 4,5,6 are returned. This is correct too.

However, when I use a function to return the namespace it doesn't work.

select extractvalue(xml,'/a/b',get_xmlns(xml)) b_value ,
      get_xmlns(xml) namespace
from   txml;
B_VALUE              NAMESPACE           
-------------------- --------------------
String1              xmlns="www.xxx.com" 
String2              xmlns="www.xxx.com" 
String3              xmlns="www.xxx.com" 
<<NULL>>             <<NULL>>            
<<NULL>>             <<NULL>>            
<<NULL>>             <<NULL>> 

The function returns the appropriate values ​​(the namespace for rows with ID 1,2,3 and NULL for the others).

But, for lines with ID 4,5,6 (even receiving NULL for the namespace) the EXTRACTVALUE function does not return the values.

What's wrong?

The CREATE FUNCTION is…

create or replace function get_xmlns (pxml XMLTYPE)
return varchar
as
 vclob    clob;
 vsize    number(12);
 voffset  number(12);
 vxmlns   varchar(4000);
begin
 select xmlserialize(content pxml as clob) into vclob from dual;
 vsize   := dbms_lob.instr  ( vclob , '"' ,dbms_lob.instr (vclob,'xmlns="') + 7) -
            dbms_lob.instr  ( vclob , 'xmlns="') + 1;
 voffset := dbms_lob.instr  ( vclob , 'xmlns="');
 vxmlns  := dbms_lob.substr (vclob,vsize,voffset);
 return vxmlns;
end;
This post has been answered by AntonScheffer-Oracle on Feb 18 2024
Jump to Answer
Comments
Post Details
Added on Feb 17 2024
7 comments
159 views