/*   
 Here is a method for logging the changes to an xml table. The script demonstrates this with the commonly
 used purchaseorder example. 

 Author:     Trevor Fairhurst, trevgf@yahoo.com
 Date:       6-Apr-2005
 Requires:   Oracle 10g or up

*/

-- Step 1 create a sequence for listing the transactions. The below code checks it doesn't exist before creating it.

DECLARE 
v_cnt NUMBER; 
begin
 Select count (*) into v_cnt from user_sequences where sequence_name = 'PURCHASEORDER_LOG_SEQ' ;
 IF v_cnt = 0 then
   Execute immediate ( 'CREATE SEQUENCE PURCHASEORDER_LOG_SEQ NOCACHE ORDER' );
 END IF;
end;
/

-- Now Create or Recreate our logging table. For the purposes of the demonstartion 
-- we will seperately log some elements as well as the whole xml doc.

DECLARE 
v_cnt NUMBER; 
begin
 Select count (*) into v_cnt from user_tables where table_name = 'PURCHASEORDER_LOG' ;
 IF v_cnt != 0 then
   Execute immediate ( 'DROP TABLE PURCHASEORDER_LOG' );
 END IF;
end;
/

purge recyclebin;

create table PURCHASEORDER_LOG as select PURCHASEORDER_LOG_SEQ.nextval LOG_SEQ, rowid PURCHASEORDER_rowid, sys_nc_rowinfo$ PURCHASEORDER_xmldata from PURCHASEORDER where rownum < 0.8;

alter table PURCHASEORDER_LOG add REFERENCE varchar2(30);
alter table PURCHASEORDER_LOG add USERNAME varchar2(128);
alter table PURCHASEORDER_LOG add operation_type varchar2(1);

-- Put this column in for use with procedures detailed later

alter table PURCHASEORDER_LOG add operation_applied varchar2(1); 

CREATE OR REPLACE TRIGGER "TRIG_LOG_PURCHASEORDER" AFTER
INSERT
OR UPDATE
OR DELETE ON "PURCHASEORDER" FOR EACH ROW DECLARE
 v_operation VARCHAR2(1);
 XMLDATA xmltype;
 v_REFERENCE VARCHAR2(1000);
 v_USER VARCHAR2(1000);
BEGIN

  IF    INSERTING THEN
        v_operation := 'I';
  ELSIF UPDATING THEN
        v_operation := 'U';
  ELSIF DELETING THEN
        v_operation := 'D';
  END IF;

  IF INSERTING OR UPDATING THEN
 XMLDATA := :new.sys_nc_rowinfo$;
 select extractValue(XMLDATA, 'PurchaseOrder/Reference') into v_REFERENCE from dual ;
 select extractValue(XMLDATA, 'PurchaseOrder/User') into v_USER from dual ;
  INSERT INTO PURCHASEORDER_LOG values ( PURCHASEORDER_LOG_SEQ.nextval,
:new.rowid,
XMLDATA,
v_REFERENCE,
v_USER,
v_operation,
'N'
);
  ELSIF DELETING THEN
 XMLDATA := :old.sys_nc_rowinfo$;
 select extractValue(XMLDATA, 'PurchaseOrder/Reference') into v_REFERENCE from dual ;
 select extractValue(XMLDATA, 'PurchaseOrder/User') into v_USER from dual ;
INSERT INTO PURCHASEORDER_LOG values ( PURCHASEORDER_LOG_SEQ.nextval,
:old.rowid,
XMLDATA,
v_REFERENCE,
v_USER,
v_operation,
'N'
 );
END IF;

END TRIG_LOG_PURCHASEORDER;
/

-- The following will need to be changed to match your schema location

insert into purchaseorder values ('<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://10.240.10.45:8091/public/purchaseorder.xsd">
  <Reference>Feed Me NOW</Reference>
  <Actions>
    <Action>
      <User>SVOLLMAN</User>
      <Date>1967-08-13</Date>
    </Action>
  </Actions>
  <Reject/>
  <Requestor>Sarah J. Bell</Requestor>
  <User>SBELL</User>
  <CostCenter>S30</CostCenter>
  <ShippingInstructions>
    <name>Sarah J. Bell</name>
    <address>  400 Oracle Parkway
                                        Redwood Shores
                                        CA
                                        94065
                                        USA
                </address>
    <telephone>650 506 7400</telephone>
  </ShippingInstructions>
  <SpecialInstructions>Air Mail</SpecialInstructions>
  <LineItems>
    <LineItem ItemNumber="1">
      <Description>A Night to Remember</Description>
      <Part Id="715515009058" UnitPrice="39.95" Quantity="2"/>
    </LineItem>
    <LineItem ItemNumber="2">
      <Description>The Unbearable Lightness Of Being</Description>
      <Part Id="37429140222" UnitPrice="29.95" Quantity="2"/>
    </LineItem>
    <LineItem ItemNumber="3">
      <Description>Sisters</Description>
      <Part Id="715515011020" UnitPrice="29.95" Quantity="4"/>
    </LineItem>
  </LineItems>
</PurchaseOrder>')
/

insert into purchaseorder values ('<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://10.240.10.45:8091/public/purchaseorder.xsd">
  <Reference>Ref53223</Reference>
  <Actions>
    <Action>
      <User>QVOLLMAN</User>
      <Date>1967-11-13</Date>
    </Action>
  </Actions>
  <Reject/>
  <Requestor>Lara J. Bull</Requestor>
  <User>SBELL</User>
  <CostCenter>S30</CostCenter>
  <ShippingInstructions>
    <name>Sarah J. Bull</name>
    <address>  400 Oracle Parkway
                                        Red Shores
                                        CA
                                        94065
                                        USA
                </address>
    <telephone>651 506 7400</telephone>
  </ShippingInstructions>
  <SpecialInstructions>Air Mail</SpecialInstructions>
  <LineItems>
    <LineItem ItemNumber="1">
      <Description>A Night to Remember</Description>
      <Part Id="715515009058" UnitPrice="39.95" Quantity="2"/>
    </LineItem>
    <LineItem ItemNumber="2">
      <Description>The Unbearable Lightness Of Being</Description>
      <Part Id="37429140222" UnitPrice="29.95" Quantity="2"/>
    </LineItem>
    <LineItem ItemNumber="3">
      <Description>Sisters</Description>
      <Part Id="715515011020" UnitPrice="29.95" Quantity="4"/>
    </LineItem>
  </LineItems>
</PurchaseOrder>')
/

insert into purchaseorder values ('<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://10.240.10.45:8091/public/purchaseorder.xsd">
  <Reference>Ref43223</Reference>
  <Actions>
    <Action>
      <User>QPULLMAN</User>
      <Date>1947-11-13</Date>
    </Action>
  </Actions>
  <Reject/>
  <Requestor>Lara J. Bull</Requestor>
  <User>ABELL</User>
  <CostCenter>S30</CostCenter>
  <ShippingInstructions>
    <name>J. Bull</name>
    <address>  400 Oracle Parkway
                                        Red Shores
                                        CAL
                                        94069
                                        USA
                </address>
    <telephone>651 506 7400</telephone>
  </ShippingInstructions>
  <SpecialInstructions>Air Mail</SpecialInstructions>
  <LineItems>
    <LineItem ItemNumber="1">
      <Description>A Night to Remember</Description>
      <Part Id="715515009058" UnitPrice="39.95" Quantity="2"/>
    </LineItem>
    <LineItem ItemNumber="2">
      <Description>The Unbearable Lightness Of Being</Description>
      <Part Id="37429140222" UnitPrice="29.95" Quantity="2"/>
    </LineItem>
    <LineItem ItemNumber="3">
      <Description>Sisters</Description>
      <Part Id="715515011020" UnitPrice="29.95" Quantity="4"/>
    </LineItem>
  </LineItems>
</PurchaseOrder>')
/

UPDATE purchaseorder t
    SET value(t) = updateXML(value(t),
                           '/PurchaseOrder/CostCenter/text()',
                            'Ref23223')
WHERE rownum < 2
/

UPDATE purchaseorder t
    SET value(t) = updateXML(value(t),
                           '/PurchaseOrder/CostCenter/text()',
                            'S' || rownum)
/

delete from purchaseorder where rownum = 1;

commit;

-- Good point to check the log.

-- An example of how this may be used, needing to repeat the changes against a copy of the table.
-- For instance with a very large xml table running an insert into as select * from with updates
-- still occuring on the primary table, this can be used to apply the updates to the secondry table.


CREATE TABLE PURCHASEORDER2 AS SELECT * FROM PURCHASEORDER;

CREATE OR REPLACE  PROCEDURE "CPRS"."PURCHASEORDERMERGE"  IS
    w_cursor_id  integer;
    sql_text varchar2(4000);

    cursor get_op is select * from PURCHASEORDER_LOG where OPERATION_APPLIED = 'N' order by LOG_SEQ ;

begin

   w_cursor_id:=dbms_sql.open_cursor;

 FOR op_rec in get_op LOOP
   IF  op_rec.operation_type = 'I' THEN
      sql_text := 'INSERT INTO PURCHASEORDER2 SELECT PURCHASEORDER_xmldata from PURCHASEORDER_LOG WHERE LOG_SEQ = '||op_rec.log_seq ;
      insert into test values (sql_text);
      commit;
      execute immediate (sql_text);
   ELSIF op_rec.operation_type = 'U' THEN
      sql_text := 'delete from PURCHASEORDER2 WHERE  (existsNode(object_value, ''/PurchaseOrder[Reference="'||op_rec.Reference||'" and User="'||op_rec.USERNAME||'"]'')=1 )';
      insert into test values (sql_text);
      commit;
      execute immediate ( sql_text);
      sql_text := 'INSERT INTO PURCHASEORDER2 SELECT PURCHASEORDER_xmldata from PURCHASEORDER_LOG WHERE LOG_SEQ = '||op_rec.log_seq ;
      insert into test values (sql_text);
      commit;
      execute immediate (sql_text);
   ELSIF op_rec.operation_type = 'D' THEN
      sql_text := 'delete from PURCHASEORDER2 WHERE  (existsNode(object_value, ''/PurchaseOrder[Reference="'||op_rec.Reference||'" and User="'||op_rec.USERNAME||'"]'')=1 )';
      insert into test values (sql_text);
      commit;
      execute immediate (sql_text);
   END IF;
 UPDATE PURCHASEORDER_LOG SET OPERATION_APPLIED = 'Y' WHERE LOG_SEQ = op_rec.log_seq ;
 END LOOP;
dbms_sql.close_cursor(w_cursor_id);

end PURCHASEORDERMERGE ;
/

-- Further options, by recording the old data for an update or insert it is a small exercise to modify the
-- above code to creare a procedure to rewind the changes made to an xml table. Very useful in tests run against
-- XML datawarehoses