Using variable to define path in XMLTable in Oracle

I run to this very same issue (both in 11.2.0.3.0 and 12.1.0.2.0). It looks like you can't use a PL/SQL variable in the place of XQuery_string at xmltable when the query string references a namespace. Note you can use a PL/SQL variable if you don't reference a namespace (see example #3 below).

The raised exception description:

LPX-01081: [XPST0081] Invalid prefix

Cause: It is a static error if a QName used in a query contains a namespace prefix that cannot be expanded into a namespace URI by using the statically known namespaces.

Action: None

If fact using a variable instead of a string literal seems to be deprecated by Oracle. Oracle support document Doc ID 1490150.1 (only available for paying customers) suggests there is a patch (the case is not exactly the same than our case but very similar) but the document also states that:

• using a variable instead of a string literal is not SQL/XML standard behaviour
• constructing XPath/XQuery during runtime has a severe performance penalty

And therefore Oracle recommends using string literals only.

My initial confusion was caused by the following conflict in Oracle's own documentation (11.2):

XMLTABLE SQL/XML Function in Oracle XML DB in XML DB Developer's Guide:

XQuery_string is a complete XQuery expression, possibly including a prolog, as a literal string.

XMLTABLE in Database SQL Language Reference:

XQuery_string is a complete XQuery expression and can include prolog declarations.

Note the missing "as a string literal" from the second quote. And of course I first read only Database SQL Language Refererence ...

The XMLTABLE documentation has been fixed in 12.1 version:

XQuery_string is a literal string. It is a complete XQuery expression and can include prolog declarations.

So the answer is that don't use a variable as XQuery_string even it compiles and in some cases seems to work.

Below you'll find minimal examples to reproduce the issue:

Example #1

This works and prints 'This is A.' as expected.

declare
  v_xml constant xmltype := xmltype('
<ns:a
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xmlns:ns="http://stackoverflow.com/users/272735/a">
  <foo><bar>This is A.</bar></foo>
</ns:a>
');
  v_content varchar2(100);
begin
  select bar into v_content
  from xmltable(
    xmlnamespaces('http://stackoverflow.com/users/272735/a' as "ns")
    ,'/ns:a/foo' passing v_xml
    columns
    bar varchar2(4000) path 'bar'
  );

  dbms_output.put_line(v_content);
end;
/

Example #2

This fails with:

ORA-19112: error raised during evaluation:
XVM-01081: [XPST0081] Invalid prefix
1   /ns:a/foo
-   ^

​

declare
  v_xml constant xmltype := xmltype('
<ns:a
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xmlns:ns="http://stackoverflow.com/users/272735/a">
  <foo><bar>This is A.</bar></foo>
</ns:a>
');
  v_xquery_string constant varchar2(100) := '/ns:a/foo';
  v_content varchar2(100);
begin
  select bar into v_content
  from xmltable(
    xmlnamespaces('http://stackoverflow.com/users/272735/a' as "ns")
    ,v_xquery_string passing v_xml
    columns
    bar varchar2(4000) path 'bar'
  );

  dbms_output.put_line(v_content);
end;
/

Example #3

This works and prints 'This is A.' as expected.

declare
  v_xml constant xmltype := xmltype('<a><foo><bar>This is A.</bar></foo></a>');
  v_xquery_string constant varchar2(100) := '/a/foo';
  v_content varchar2(100);
begin
  select bar into v_content
  from xmltable(
    v_xquery_string passing v_xml
    columns
    bar varchar2(4000) path 'bar'
  );

  dbms_output.put_line(v_content);
end;
/

Seems you are using a wrong XML namespace prefix in your XML.
The used prefix is soap but the namespace declaration is

xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/

try to change this to soap

xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/

But this does not explain the described problem