Oracle SQL

Tag Archives: xmlnamespace

Bug with xmltable, xmlnamespaces and xquery_string specified using bind variable

Posted on by Posted in bug, oracle 3 Comments

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…