oracle-databasexquery-sqloracle-xml-db

Using the XQUERY node function to extract values into rows


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.


Solution

  • 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:

    enter image description here