The extract() and extractclob() XML functions

Evaluates an XPATH expression on an XML column, document, or string. These functions are identical except that extractclob() returns a CLOB instead of LVARCHAR.

Purpose

Returns an XML fragment of the evaluated XML column, document, or string. For details on XPATH, see http://www.w3.org/TR/xpath.

The extract() syntax

The extractclob() syntax

Parameters

xml_string: The XML string or document to evaluate.
xpath_expression: An XPATH expression. For extract(), the string or document size cannot exceed 32739. For larger strings or documents, use extractclob().
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 evaluates the XML contained in column col2 of table tab and returns the given name for Jason Ma.

SELECT extract(col2, '/personnel/person[@id="Jason.Ma"]/name/given') 
FROM tab;

<given>Jason</given>

Example 2

This example is similar to the first, except the entire name is returned.

SELECT extract(col2, '/personnel/person[@id="Jason.Ma"]/name') 
FROM tab;

<name>
<family>Ma</family>
<given>Jason</given>
</name>

Example 3

In this example, only the second column contains XML.

SELECT warehouse_name, extract(warehouse_spec, '/Warehouse/Docks')::lvarchar(256)
"Number of Docks"
FROM warehouses
WHERE warehouse_spec IS NOT NULL;

WAREHOUSE_NAME           Number of Docks
--------------------     --------------------
Liverpool, England       <Docks>2</Docks>
Perth, Australia         <Docks>1</Docks>
Buenos Aires, Argentina  <Docks>4</Docks>
Seattle, USA             <Docks>3</Docks>