Oracle - Iterate over XMLTYPE And Return Nodes As Individual Rows

Question

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.

<c>
  <f n="VersNo" b="1" a="2"/>
  <f n="LstDate" b="20160215" a="20160217"/>
  <f n="FileSweepId" b="Test" a="Test1"/>
</c>

• c stands for changes
• f stands for field
• n attribute stands for name (as in name of the field)
• b stands for before value
• a stands for after value

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.

That is each f node in each of the relevant audit record needs to become a row in the report.

The report is generated by our app. What the app can take for generating the report is one of the following:

1. A SQL Query
2. 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.

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.

1. The first problem I am facing is how to iterate over the f nodes in the procedure.

2. Secondly, I need to figure out how to return these f nodes along with other info from audit records in out_cursor.

   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 < 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;
   

The above PL/SQL at present errors with:

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

Answer 1

Why don't you use simple SQL, with chained XMLTable functions that extract reqired fields ?
Look at simple example:

CREATE TABLE audit1(
  changes CLOB
);

INSERT INTO audit1 VALUES( '<c>
  <f n="VersNo" b="1" a="2"/>
  <f n="LstDate" b="20160215" a="20160217"/>
  <f n="FileSweepId" b="Test" a="Test1"/>
</c>'
);
INSERT INTO audit1 VALUES(
'<c>
  <f n="VersNo" b="22" a="32"/>
  <f n="LstDate" b="20160218" a="2016020"/>
  <f n="FileSweepId" b="Test 555" a="Test1234"/>
</c>'
);
commit;

and now:

SELECT rec_no, rn, n, b, a
FROM ( select rownum rec_no, a.* FROM audit1 a ),
     XMLTable( '/c'
               passing xmltype( changes )
               columns f_fields xmltype path './f'
              ) x1,
     XMLTable( '/f'
                 passing x1.f_fields
                 columns rn for ordinality,
                         n varchar2(20) path './@n',
                         b varchar2(20) path './@b',
                         a varchar2(20) path './@a'
                )   

    REC_NO         RN N                    B                    A                  
---------- ---------- -------------------- -------------------- --------------------
         1          1 VersNo               1                    2                    
         1          2 LstDate              20160215             20160217             
         1          3 FileSweepId          Test                 Test1                
         2          1 VersNo               22                   32                   
         2          2 LstDate              20160218             2016020              
         2          3 FileSweepId          Test 555             Test1234             

 6 rows selected 

Answer 2

I was able to get rid of the error by modifying the plsql to:

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 < 2 )
  LOOP
    dbms_output.put_line('parsing fields from: ' || item.fields.getStringVal());
    dbms_output.put_line('name||beforevalue||aftervalue');
    FOR field in (select n, b, a  from   XMLTable('//f'  passing  item.fields columns n varchar2(30) path '@n', b varchar(30) path '@b',  a varchar(30) path '@a' ))
      LOOP
         dbms_output.put_line(field.n || '|| ' || field.b || '|| '  || field.a);
     END LOOP;
  END LOOP;
END;

So I am now able to iterate over the fields. But not sure yet how I can return info from the fields in the sys ref cursor. Example output from above plsql:

parsing fields from: <f n="VersNo" b="1" a="2"/><f n="LstDate" b="20160215" a="20160217"/><f n="FileSweepId" b="Test" a="Test1"/>
name||beforevalue||aftervalue
VersNo|| 1|| 2
LstDate|| 20160215|| 20160217
FileSweepId|| Test|| Test1