XML_EXTRACT
The XML_EXTRACT function extracts the XML nodes that are specified by an XPath expression.
Syntax
XMLEXTRACT(xmlfile, xpath_expression)
Example
select xml_extract('<?xml version="1.0" standalone="no"?><emps><emp><interests><interest>i1</interest><interest>i2</interest><interest>i3</interest></interests></emp></emps>','/emps/emp/interests/interest/text()') a1
from
{path1 as table1}
The above example extracts the value of /emps/emp/interests/interest node.