PLSQL是否从XML读取值?

问题描述

我有一个带有XML代码的字符串，我想在PL/SQL中将其值提取到一个变量中.

I have a string with XML code, I want to extract a value from it in PL/SQL to a variable.

XML非常简单，并且不会与此不同:

The XML is very simple and will not be different than this:

<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
    <s:Body>
        <SOAProxyResponse xmlns="http://tempuri.org/">
            <SOAProxyResult>
                There is 23142 Files!
            </SOAProxyResult>
        </SOAProxyResponse>
    </s:Body>
</s:Envelope

如何在上面的示例中将值"有23142个文件！"转换为变量?

How I can get the value "There is 23142 Files!" in the example above into a variable?

推荐答案

您可以使用函数来获取该值.此函数带有两个或三个参数:

You can use the EXTRACTVALUE function to obtain this value. This function takes two or three parameters:

• 包含XML文档的XMLTYPE对象.
• 一个XPath表达式，用于标识我们想要的值在XML中的位置.
• (可选)一个额外的字符串，用于将名称空间前缀绑定到URI.

在下面的查询中，我将上面显示的XML作为字符串，并从中创建了XMLTYPE对象.然后，我使用EXTRACTVALUE来获取您要求的值:

In the query below, I've taken the XML you presented above as a string and have created an XMLTYPE object from it. I then use EXTRACTVALUE to get the value you asked for:

SELECT EXTRACTVALUE(XMLTYPE(
    '<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
        <s:Body>
            <SOAProxyResponse xmlns="http://tempuri.org/">
                <SOAProxyResult>
                    There is 23142 Files!
                </SOAProxyResult>
            </SOAProxyResponse>
        </s:Body>
    </s:Envelope>'), '//SOAProxyResult', 'xmlns="http://tempuri.org/"') AS result
  FROM dual;

XPath表达式//SOAProxyResult仅返回文档中的所有SOAProxyResult元素. EXTRACTVALUE的第三个参数将默认名称空间绑定到http://tempuri.org/.这是必需的，因为XML文档中的SOAProxyResult元素位于此命名空间之内.

The XPath expression //SOAProxyResult merely returns all SOAProxyResult elements in the document. The third argument to EXTRACTVALUE binds the default namespace to http://tempuri.org/. This is necessary because the SOAProxyResult element in your XML document is within this namespace.

如果运行此查询，则会得到以下输出:

If I run this query, I get the following output:

RESULT
--------------------------------------------------------------------------------

                    There is 23142 Files!

从这里开始，希望可以对查询的结果进行简单的修改，以使其成为变量.

From here, it should hopefully be a trivial modification to put the result of this query into a variable.

这篇关于PLSQL是否从XML读取值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！