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