在Oracle中使用变量在XMLTable中定义路径 [英] Using variable to define path in XMLTable in Oracle

查看：97 发布时间：2020/5/22 2:27:16 xml oracle xpath xmltable

本文介绍了在Oracle中使用变量在XMLTable中定义路径的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在学习如何在Oracle 11g中使用XMLQuery和XMLtable功能，并且在使用XMLTable查询时，我试图在XPath中使用变量而不是特定的字符串. 例如:

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;

我使用此查询来解析XML.但是在某些情况下，XML是不同的，并且我有一个新的XPath.我试图将XPath放在这样的变量中:

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

并替换为XMLTable查询:

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;

我得到了错误:

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?

推荐答案

我遇到了同样的问题(在11.2.0.3.0和12.1.0.2.0中都如此).看来您不能在

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]无效的前缀

LPX-01081: [XPST0081] Invalid prefix

原因:如果查询中使用的QName包含无法使用静态已知的名称空间扩展为名称空间URI的名称空间前缀，则是静态错误.

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.

操作:无

如果Oracle不赞成使用变量而不是字符串文字的事实. Oracle支持文档Doc ID 1490150.1(仅适用于付费客户)建议存在一个补丁(此案例与我们的案例不完全相同，但非常相似)，但该文档还指出:

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:

使用变量而不是字符串文字不是SQL/XML标准行为
在运行时构造XPath/XQuery会严重影响性能

因此，Oracle建议仅使用字符串文字.

And therefore Oracle recommends using string literals only.

最初的困惑是由Oracle自己的文档(11.2)中的以下冲突引起的:

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

Oracle XML DB中的XMLTABLE SQL/XML函数在 XML DB开发人员指南中:

XQuery_string 是完整的XQuery表达式，可能包含序言作为文字字符串.

XMLTABLE ="https://docs.oracle.com/cd/E11882_01/server.112/e41084/toc.htm" rel ="nofollow noreferrer">数据库SQL语言参考:

XMLTABLE in Database SQL Language Reference:

XQuery_string 是完整的XQuery表达式，可以包含序言声明.

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

请注意第二个引号中缺少的作为字符串文字" .当然，我首先只阅读数据库SQL语言参考 ...

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

XMLTABLE文档已在 12.1版本中进行了修复 :

The XMLTABLE documentation has been fixed in 12.1 version:

XQuery_string 是文字字符串.它是一个完整的XQuery表达式，可以包含序言声明.

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

所以答案是即使编译后也不要将变量用作 XQuery_string ，

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:

示例1

这有效并打印"This is A".如预期的那样.

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

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

示例#3

这有效并打印"This is A".如预期的那样.

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

这篇关于在Oracle中使用变量在XMLTable中定义路径的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文

在Oracle中使用变量在XMLTable中定义路径 [英] Using variable to define path in XMLTable in Oracle

问题描述

推荐答案

相关文章

数据库最新文章

热门教程

热门工具

登录关闭

在Oracle中使用变量在XMLTable中定义路径 [英] Using variable to define path in XMLTable in Oracle

问题描述

推荐答案

相关文章

数据库最新文章

热门教程

热门工具

登录 关闭

登录关闭