title: Using the XQUERY node function to extract values into rows
body: <p>My question is identical to this <a href="https://stackoverflow.com/questions/10948870/how-to-extract-list-of-values-into-rows-from-xml-using-xquery">one</a> but instead of SQL Server I am using Oracle 12.1. </p>
  
  <p>I would like to extract all the name elements from the following xml document:</p>
  
  <pre><code>&lt;ci5&gt;
     &lt;employee&gt;
        &lt;name&gt;George Cowley&lt;/name&gt;  
        &lt;salary via="B"&gt;80000&lt;/salary&gt;
     &lt;/employee&gt;
     &lt;employee&gt;
        &lt;name&gt;William Bodie&lt;/name&gt;  
        &lt;salary via="C"&gt;40000&lt;/salary&gt;
     &lt;/employee&gt;
     &lt;employee&gt;
        &lt;name&gt;Ray Doyle&lt;/name&gt;  
        &lt;salary via="C"&gt;40000&lt;/salary&gt;
     &lt;/employee&gt;
  &lt;/ci5&gt;  
  </code></pre>
  
  <p>The output I want is three separate rows such as:</p>
  
  <pre><code>&lt;name&gt;George Cowley&lt;/name&gt;
  &lt;name&gt;William Bodie&lt;/name&gt;
  &lt;name&gt;Ray Doyle&lt;/name&gt;
  </code></pre>
  
  <p>Using the following queries </p>
  
  <pre><code>SELECT XMLQUERY('ci5/employee/name' 
                    PASSING BY VALUE XMLTYPE.CREATEXML('&lt;ci5&gt;&lt;employee&gt;&lt;name&gt;George Cowley&lt;/name&gt;&lt;salary via="B"&gt;80000&lt;/salary&gt;&lt;/employee&gt;&lt;employee&gt;&lt;name&gt;William Bodie&lt;/name&gt;&lt;salary via="C"&gt;40000&lt;/salary&gt;&lt;/employee&gt;&lt;employee&gt;&lt;name&gt;Ray Doyle&lt;/name&gt;&lt;salary via="C"&gt;40000&lt;/salary&gt;&lt;/employee&gt;&lt;/ci5&gt;') RETURNING CONTENT) x
     FROM dual;  
  
   SELECT XMLQUERY('for $i in /ci5
                   return $i/employee/name'
                   PASSING BY VALUE XMLTYPE.CREATEXML('&lt;ci5&gt;&lt;employee&gt;&lt;name&gt;George Cowley&lt;/name&gt;&lt;salary via="B"&gt;80000&lt;/salary&gt;&lt;/employee&gt;&lt;employee&gt;&lt;name&gt;William Bodie&lt;/name&gt;&lt;salary via="C"&gt;40000&lt;/salary&gt;&lt;/employee&gt;&lt;employee&gt;&lt;name&gt;Ray Doyle&lt;/name&gt;&lt;salary via="C"&gt;40000&lt;/salary&gt;&lt;/employee&gt;&lt;/ci5&gt;') RETURNING CONTENT) x
     FROM dual;  
  </code></pre>
  
  <p>the names are shown, but the output is all on one line:</p>
  
  <pre><code>&lt;name&gt;George Cowley&lt;/name&gt;&lt;name&gt;William Bodie&lt;/name&gt;&lt;name&gt;Ray Doyle&lt;/name&gt;
  </code></pre>
  
  <p>It looks like I need to use the <code>NODE</code> function but I don't how to use it within an Oracle context. </p>