title: Oracle - Iterate over XMLTYPE And Return Nodes As Individual Rows
body: <p>We have a table called audit1. It has a column 'changes' that contains XML as blob. The xml looks like below. The audit table basically records changes that happen to other tables via our app's UI.</p>
  
  <pre><code>&lt;c&gt;
    &lt;f n="VersNo" b="1" a="2"/&gt;
    &lt;f n="LstDate" b="20160215" a="20160217"/&gt;
    &lt;f n="FileSweepId" b="Test" a="Test1"/&gt;
  &lt;/c&gt;
  </code></pre>
  
  <ul>
  <li>c stands for changes</li>
  <li>f stands for field</li>
  <li>n attribute stands for name (as in name of the field)</li>
  <li>b stands for before value</li>
  <li>a stands for after value</li>
  </ul>
  
  <p>I need to create a report that lists all changes that have occurred since a given date to certain tables. Once I have the audit1 records I am interested in, I need to list all the f nodes in the report.</p>
  
  <p>That is each f node in each of the relevant audit record needs to become a row in the report.</p>
  
  <p>The report is generated by our app. What the app can take for generating the report is one of the following:</p>
  
  <ol>
  <li>A SQL Query</li>
  <li>Or name of a stored procedure that must return its result in a sys ref cursor which will be passed to the procedure when the app calls it. The ref cursor will be called out_cursor.</li>
  </ol>
  
  <p>I can't think of a way to achieve this via a single sql query. So, I am going down the road of writing a stored proc. </p>
  
  <ol>
  <li><p>The first problem I am facing is how to iterate over the f nodes in the procedure.</p></li>
  <li><p>Secondly, I need to figure out how to return these f nodes along with other info from audit records in out_cursor.</p>
  
  <p><pre>
  BEGIN 
       FOR item IN (SELECT auditno, xmltype(changes, 1) as changes, extract(xmltype(changes, 1), '/c/f') as fields from audit1 where runlistno is null  and rownum &lt; 2 )
        LOOP
         dbms_output.put_line(item.auditno || ' ' || item.changes.getStringVal() || ' '  || item.fields.getStringVal());
         -- stumped about how to iterate over the f nodes
         --FOR field in ('select extractvalue(object_value, '/') x  FROM TABLE(XMLSequence(' + item.fields.getStringVal() + ') ')
    FOR field in (select f  from   XMLTable('for $i in / return $i'  passing  item.fields columns  f varchar2(200) path 'f'))
    LOOP
       dbms_output.put_line(field.f);
    END LOOP;
   END LOOP;
  END;
  </pre></p></li>
  </ol>
  
  <p>The above PL/SQL at present errors with:</p>
  
  <blockquote>
    <p>ORA-19114: XPST0003 - error during parsing the XQuery expression: 
    LPX-00801: XQuery syntax error at 'i' 1   for $i in / return $i
    -                      ^ ORA-06512: at line 6</p>
  </blockquote>