[SQL] Oracle get data from XML object node

In Oracle, if you need to extract the node value from an XML object you may use the EXTRACTVALUE function specifying an XPath predicate.

Suppose your XML object has the following schema:

<?xml version="1.0" encoding="utf-8"?> <root> <node>value</node> </root>


and is of type XMLType, then you would:

SELECT EXTRACTVALUE(
    your_object,
    '//root/node/text()'
) node_value
FROM dual;
You may optionally add a namespace argument to the EXTRACTVALUE function:

<?xml version="1.0" encoding="utf-8"?> <root xmlns:schema1="schema1_URI" xmlns:schema2="schema2_URI"> <schema1:node>value</schema1:node> </root>

SELECT EXTRACTVALUE(
    your_object,
    '//root/node/text()',
    'xmlns:schema1="schema1_URI" xmlns:schema2="schema2_URI"'
) node_value
FROM dual;

Note that the /text() part is needed ONLY if the node value is not a String, otherwise you may omit it.

If your XML object has multiple nodes, you can query it with XMLQuery; if it's stored inside a table column, you may unnest it with XMLTABLE to create a virtual table to query on, as you would unnest a non-XML object with TABLE().