Today I was asked about strange problem: xmltable does not return data, if xquery specified by bind variable and xml data has xmlnamespaces:
SQL> var x_path varchar2(100); SQL> var x_xml varchar2(4000); SQL> col x format a100; SQL> begin 2 :x_path:='/table/tr/td'; 3 :x_xml :=q'[ 4 <table xmlns="http://www.w3.org/tr/html4/"> 5 <tr> 6 <td>apples</td> 7 <td>bananas</td> 8 </tr> 9 </table> 10 ]'; 11 end; 12 / PL/SQL procedure successfully completed. SQL> select 2 i, x 3 from xmltable( xmlnamespaces(default 'http://www.w3.org/tr/html4/'), 4 :x_path -- bind variable 5 --'/table/tr/td' -- same value as in the variable "X_PATH" 6 passing xmltype(:x_xml) 7 columns i for ordinality, 8 x xmltype path '.' 9 ); no rows selected
But if we comment bind variable and comment out literal x_query ‘/table/tr/td’, query will return data:
SQL> select
2 i, x
3 from xmltable( xmlnamespaces(default 'http://www.w3.org/tr/html4/'),
4 --:x_path -- bind variable
5 '/table/tr/td' -- same value as in the variable "X_PATH"
6 passing xmltype(:x_xml)
7 columns i for ordinality,
8 x xmltype path '.'
9 );
I X
---------- -------------------------------------------------------------------
1 <td xmlns="http://www.w3.org/tr/html4/">apples</td>
2 <td xmlns="http://www.w3.org/tr/html4/">bananas</td>
2 rows selected.
The only workaround I found is the specifying any namespace in the x_query – ‘/*:table/*:tr/*:td’
SQL> exec :x_path:='/*:table/*:tr/*:td'
PL/SQL procedure successfully completed.
SQL> select
2 i, x
3 from xmltable( xmlnamespaces(default 'http://www.w3.org/tr/html4/'),
4 :x_path -- bind variable
5 passing xmltype(:x_xml)
6 columns i for ordinality,
7 x xmltype path '.'
8 );
I X
---------- -------------------------------------------------------------------
1 <td xmlns="http://www.w3.org/tr/html4/">apples</td>
2 <td xmlns="http://www.w3.org/tr/html4/">bananas</td>
2 rows selected.
It’s quite ugly solution, but I’m not sure whether there is another solution…