EXTRACT (XML)
Description of the illustration extract_xml.gif
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).
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.