My question is identical to this one but instead of SQL Server I am using Oracle 12.1.
I would like to extract all the name elements from the following xml document:
<ci5>
<employee>
<name>George Cowley</name>
<salary via="B">80000</salary>
</employee>
<employee>
<name>William Bodie</name>
<salary via="C">40000</salary>
</employee>
<employee>
<name>Ray Doyle</name>
<salary via="C">40000</salary>
</employee>
</ci5>
The output I want is three separate rows such as:
<name>George Cowley</name>
<name>William Bodie</name>
<name>Ray Doyle</name>
Using the following queries
SELECT XMLQUERY('ci5/employee/name'
PASSING BY VALUE XMLTYPE.CREATEXML('<ci5><employee><name>George Cowley</name><salary via="B">80000</salary></employee><employee><name>William Bodie</name><salary via="C">40000</salary></employee><employee><name>Ray Doyle</name><salary via="C">40000</salary></employee></ci5>') RETURNING CONTENT) x
FROM dual;
SELECT XMLQUERY('for $i in /ci5
return $i/employee/name'
PASSING BY VALUE XMLTYPE.CREATEXML('<ci5><employee><name>George Cowley</name><salary via="B">80000</salary></employee><employee><name>William Bodie</name><salary via="C">40000</salary></employee><employee><name>Ray Doyle</name><salary via="C">40000</salary></employee></ci5>') RETURNING CONTENT) x
FROM dual;
the names are shown, but the output is all on one line:
<name>George Cowley</name><name>William Bodie</name><name>Ray Doyle</name>
It looks like I need to use the NODE
function but I don't how to use it within an Oracle context.
This Oracle white paper answered the question, I needed to use XMLTABLE which
...maps the result of an XQuery evaluation into relational rows and columns...
Changing my query to use XMLTABLE
SELECT x.ename
FROM XMLTABLE('/ci5/employee/name'
PASSING XMLTYPE.CREATEXML('<ci5><employee><name>George Cowley</name><salary via="B">80000</salary></employee><employee><name>William Bodie</name><salary via="C">40000</salary></employee><employee><name>Ray Doyle</name><salary via="C">40000</salary></employee></ci5>')
COLUMNS ename VARCHAR2(30) PATH '/name') x
gives the following output in SQL Developer: