The extractvalue() and extractvalueclob() XML functions

Returns the value of the XML node in contrast to extract(), which returns the XML node.

Purpose

Returns a value from evaluated XML column, document, or string. For details on XPATH, see http://www.w3.org/TR/xpath.

The extractvalue() syntax

extractvalue(xml_string ,xpath_expression)

The extractvalueclob() syntax

extractvalueclob(xml_string ,xpath_expression)

Parameters

xml_string
The XML string or document to evaluate.
xpath_expression
An XPATH expression. For extractvalue(), the string or document size cannot exceed 32739. For larger strings or documents, use extractvalueclob().

Specify an absolute XPath_string with an initial slash. Omit the initial slash to indicate a path relative to the root node. If no match is found, these functions return an empty string.

Example 1

This example returns the value given name of the person who is identified in the XPATH expression. No XML tags are returned.
	SELECT extractvalue(col2, '/personnel/person[3]/name/given') FROM tab;

The output is the given name: Jason

Example 2

This example returns the number of docks in several cities.
SELECT warehouse_name, 
extractvalue(e.warehouse_spec, '/Warehouse/Docks')
"Docks"
FROM warehouses e 
WHERE warehouse_spec IS NOT NULL;
WAREHOUSE_NAME          Docks
--------------------    ------------
Liverpool, England      2
Perth, Australia        1
Buenos Aires, Argentina 
Seattle, USA            3