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?
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
