Extract value from XMLTYPE using PL/SQL, SQL, Oracle

Question

I have a PL/SQL Function that returns a XMLTYPE and I would like to extract the value from the "result" tag, which is in this case is "0". XMLTYPE is below.

<?xml version='1.0' encoding='UTF-8'?>
<S:Envelope xmlns:S="http://schemas.xmlsoap.org/soap/envelope/">
<S:Body><ns2:runSeriatimValuationResponse xmlns:ns2="http://seriatim.ipvfbm.pbgc.gov/">
<result>0</result>
</ns2:runSeriatimValuationResponse>
</S:Body>
</S:Envelope>

Thanks for the help - Much appreciated it

Answer 1

This query return the value of result tag (0 in this case):

SELECT XMLTYPE('<?xml version=''1.0'' encoding=''UTF-8''?>
<S:Envelope xmlns:S="http://schemas.xmlsoap.org/soap/envelope/">
<S:Body><ns2:runSeriatimValuationResponse xmlns:ns2="http://seriatim.ipvfbm.pbgc.gov/">
<result>0</result>
</ns2:runSeriatimValuationResponse>
</S:Body>
</S:Envelope>').EXTRACT('//result/text()').getStringVal() result
FROM dual;

In your case you don't need to convert to XMLTYPE, just apply EXTRACT('//result/text()').getStringVal() to the result of your function