Today I was asked about strange problem: xmltable does not return data, if xquery specified by bind variable and xml data has xmlnamespaces:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | 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 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | SQL> select 2 i, x 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---------- -------------------------------------------------------------------2 rows selected. |
The only workaround I found is the specifying any namespace in the x_query – ‘/*:table/*:tr/*:td’
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | SQL> exec :x_path:='/*:table/*:tr/*:td'PL/SQL procedure successfully completed.SQL> select 2 i, x 4 :x_path -- bind variable 5 passing xmltype(:x_xml) 6 columns i for ordinality, 7 x xmltype path '.' 8 ); I X---------- -------------------------------------------------------------------2 rows selected. |
It’s quite ugly solution, but I’m not sure whether there is another solution…