PLSQL read value from XML?

Question

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

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

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

Answer 1

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

• an XMLTYPE object that contains an XML document.
• an XPath expression that identifies where in the XML the value we want is.
• (optional) an extra string that binds namespace prefixes to URIs.

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;

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.

Answer 2

There was a good article in the Oracle Magazine a while back - which may help you:

http://www.oracle.com/oramag/oracle/01-jul/o41xml.html

Another thing that we use quite commonly here is the XMLType API, but of course it depends on how your are storing / holding your XML data:

http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96616/arxml24.htm