Purpose
EXTRACT() extracts an XML fragment based on an XPath. All nodes matching the XPath are concatenated to generate this XML fragment.
Syntax
EXTRACT(XMLType_instance, XPath_string [, namespace_string ])
Return type
The return type is XMLType.
Parameters
| Parameter | Description |
|---|---|
| XMLType_instance | The XMLType data, which can be an XMLType column or an expression that returns XMLType data. |
| XPath_string | The XPath used for node search. |
| namespace_string | The XML namespace. |
Examples
DROP TABLE xml_test;
CREATE TABLE xml_test (id NUMBER, c1 XMLType);
INSERT INTO xml_test VALUES (1, '<a>aaa</a>');
INSERT INTO xml_test VALUES (2, '<a><b>aaa</b><b>bbb</b></a>');
INSERT INTO xml_test VALUES (3, '<a a1="a1">aaa</a>');
INSERT INTO xml_test VALUES (4, '<a><b b1="b1" b2="b2">bbb</b></a>');
INSERT INTO xml_test VALUES (5, '<a><b b1="b1" b2="b2">bbb1</b><b b1="b1" b2="b2">bbb2</b></a>');
INSERT INTO xml_test VALUES (6, '<a xmlns="ns1" xmlns:f="ns2"><f:b b1="b1" b2="b2">bbb1</f:b><b b1="b1" b2="b2">bbb2</b></a>');
obclient> SELECT EXTRACT(c1, '/a/f:b', 'xmlns="ns1" xmlns:f="ns2"') as RES
FROM xml_test WHERE id = 6;
+----------------------------------+
| RES |
+----------------------------------+
| <f:b b1="b1" b2="b2">bbb1</f:b>
|
+----------------------------------+
1 row in set
obclient> SELECT EXTRACT(c1, '/a/h:b/text()', 'xmlns="ns1" xmlns:h="ns2"') as RES FROM xml_test WHERE id = 6;
+------+
| RES |
+------+
| bbb1 |
+------+
1 row in set