Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-01 |
|
|
View PDF |
Syntax
extract_xml::=
Purpose
EXTRACT
(XML) is similar to the EXISTSNODE
function. It applies a VARCHAR2
XPath string and returns an XMLType
instance containing an XML fragment. You can specify an absolute XPath_string
with an initial slash or a relative XPath_string
by omitting the initial slash. If you omit the initial slash, the context of the relative path defaults to the root node. The optional namespace_string
must resolve to a VARCHAR2
value that specifies a default mapping or namespace mapping for prefixes, which Oracle Database uses when evaluating the XPath expression(s).
Examples
The following example extracts the value of the /Warehouse/Dock
node of the XML path of the warehouse_spec
column in the sample table oe.warehouses
:
SELECT warehouse_name, EXTRACT(warehouse_spec, '/Warehouse/Docks') "Number of Docks" FROM warehouses WHERE warehouse_spec IS NOT NULL; WAREHOUSE_NAME Number of Docks -------------------- -------------------- Southlake, Texas <Docks>2</Docks> San Francisco <Docks>1</Docks> New Jersey <Docks/> Seattle, Washington <Docks>3</Docks>
Compare this example with the example for EXTRACTVALUE, which returns the scalar value of the XML fragment.