Using variable to define path in XMLTable in Oracle

Question

I'm learning how to use XMLQuery and XMLtable features in Oracle 11g and I'm trying to use a variable instead of a specific string in XPath, when using a XMLTable query. For example:

DECLARE
   px_return    XMLTYPE
      := XMLTYPE (
            '<?xml version="1.0" encoding="UTF-8"?>
<soap:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
    <SOAP:Header xmlns:SOAP="http://schemas.xmlsoap.org/soap/envelope/">
    </SOAP:Header>
    <SOAP:Body xmlns:SOAP="http://schemas.xmlsoap.org/soap/envelope/">
        <n0:validatePolicyCancelResponse xmlns:n0="urn:enterprise.com/ws/SAP/Finantial/MaintainMandate/V1">
            <return>
                <messageType>E</messageType>
            </return>
        </n0:validatePolicyCancelResponse>
    </SOAP:Body>
</soap:Envelope>');

   lv_msgType   VARCHAR2 (20);
   lv_urlString VARCHAR2 (40);
BEGIN
   SELECT Return.msgType
     INTO lv_msgType
     FROM XMLTABLE (
             xmlnamespaces (
                DEFAULT 'enterprise.com/ws/SAP/Finantial/MaintainMandate/V1',
                'http://schemas.xmlsoap.org/soap/envelope/' AS "soap",
                'http://schemas.xmlsoap.org/soap/envelope/' AS "SOAP",
                'enterprise.com/ws/SAP/Finantial/MaintainMandate/V1' AS "n0"),
             '//soap:Envelope/SOAP:Body/n0:validatePolicyCancelResponse/return'
             PASSING px_return
             COLUMNS msgType VARCHAR2 (1) PATH 'messageType') Return;


   DBMS_OUTPUT.put_line ('Message type: ' || lv_msgType);
END;

I use this query to parse the XML. But in some cases, the XML is different and I have a new XPath. I tried to put the XPath in a variable like this:

lv_urlString :=
      '//soap:Envelope/SOAP:Body/n0:validatePolicyCancelResponse/return';

and substitute in the XMLTable query as this:

SELECT Return.msgType
     INTO lv_msgType
     FROM XMLTABLE (
             xmlnamespaces (
                DEFAULT 'enterprise.com/ws/SAP/Finantial/MaintainMandate/V1',
                'http://schemas.xmlsoap.org/soap/envelope/' AS "soap",
                'http://schemas.xmlsoap.org/soap/envelope/' AS "SOAP",
                'enterprise.com/ws/SAP/Finantial/MaintainMandate/V1' AS "n0"),
             lv_urlString
             PASSING px_return
             COLUMNS msgType VARCHAR2 (1) PATH 'messageType') Return;

I get the error:

ORA-19112: error raised during evaluation: 
XVM-01081: [XPST0081] Invalid prefix

Can anyone help me to do this by reusing the same query, but applying different paths?

Perhaps `RETURN` is considered as reserved word, try something else, e.g. `ret` — Wernfried Domscheit, Mar 15 '16 at 14:49
Sorry but RETURN it's ok when used in the path as string in the query — milheiros, Mar 15 '16 at 16:38

user272735 · Answer 1 · 2017-01-04T11:35:33.303

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;
/

hr_117 · Answer 2 · 2016-03-15T14:08:20.140

1

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

edited Mar 15 '16 at 14:08

answered Mar 15 '16 at 14:03

hr_117

9,589
1
18
23

Using variable to define path in XMLTable in Oracle

2 Answers2

Linked