How to default an XML element in an XML document before inserting it into the database

Date: 30-May-2003

Objective

After reading this document, you should be able to:

• Default an XML element in an XML document with a value before inserting it into:
  • An XMLType table.
  • An XMLType column.
    
• Test the SQL Script.

Software Requirements

• Oracle9i Database Release 2 or later. This can be downloaded from here.

Description

XML documents can be stored in a column of XMLType or in a table of XMLType. If the XML document that has to be inserted has an element for which you want to provide a default value, here is one of the ways how you can do it:

• Create a before insert trigger in the database which will fire for every row inserted into the table.
• Use the updatexml() function in the trigger code to default the value of an XML element.
  

Sample scenario:

Let us consider the scenarios where XML documents are stored in a table of XMLType and a column of XMLType. To run the SQL scripts, connect to Oracle database from a SQL*Plus client as any user.
For example: SQL> sqlplus scott/tiger@sid

1) XMLType table storing XML Documents:

For example: purchaseorder is an XMLType table in the database. The purchaseorder XML document has <Date> as one of its elements which has to be defaulted to the system date. Following is a typical code that can be used to achieve this.

• Create the purchaseorder table in the database.

SQL> CREATE TABLE purchaseorder OF XMLType;

• Create a before insert trigger to default the <Date> element value to sysdate.

SQL> CREATE OR REPLACE TRIGGER default_po_date
     BEFORE INSERT ON purchaseorder
     FOR EACH ROW

     DECLARE
       -- XMLType variable to hold the current(to be inserted) XMLType record.
       xmldata XMLType;

       -- XMLType variable to hold the updated xml document with <Date> element
       -- defaulted to sysdate.
       tmpxml  XMLType;

     BEGIN
       -- get the current xmltype record.       
       xmldata := :new.sys_nc_rowinfo$;
      
       -- check if the <Date> element exists.
       IF xmldata.existsnode('/PurchaseOrder/Actions/Action/Date') = 1 THEN


         -- Update the <Date> element value to sysdate
         SELECT UPDATEXML(xmldata, '/PurchaseOrder/Actions/Action/Date',
                     '<Date>'|| to_char(sysdate, 'DD-MON-YYYY') || '</Date>')
         INTO tmpxml FROM dual;

         -- Assign the updated XML document to the current record.
         :new.sys_nc_rowinfo$ := tmpxml;
       END IF;

    END;
/

• Insert a record to the purchaseorder XMLTYPE table.

SQL> INSERT INTO purchaseorder VALUES (
     XMLTYPE('<?xml version="1.0"?>
     <PurchaseOrder>
      <Reference>Savitha</Reference>
       <Actions>
        <Action>
         <User>SCOTT</User>
         <User>ADAMS</User>
         <Date></Date>
        </Action>
       </Actions>
     </PurchaseOrder>'));

• Check to see if the record has been inserted into the table in the database with the <Date> element defaulted to sysdate by running the following query:

SQL> SELECT VALUE(t) FROM purchaseorder t;
     VALUE(T)
    ---------------------------------------
    <PurchaseOrder>
     <Reference>Savitha</Reference>
      <Actions>
       <Action>
        <User>SCOTT</User>
        <User>ADAMS</User>
        <Date>30-MAY-2003</Date>
       </Action>
      </Actions>
     </PurchaseOrder>

• Create the po_xml table in the database.
  
  

SQL> CREATE TABLE po_xml (id NUMBER(10), podoc sys.XMLType);

• Create a before insert trigger to default the <Date> element value to sysdate.

SQL> CREATE OR REPLACE TRIGGER default_po_xml_date
     BEFORE INSERT ON po_xml
     FOR EACH ROW

     DECLARE
       -- XMLType variable to hold the current(to be inserted) XMLType record.
       xmldata XMLType;

       -- XMLType variable to hold the updated xml document with <Date> element
       -- defaulted to sysdate.
       tmpxml  XMLType;

     BEGIN
       -- get the current xmltype record.       
       xmldata := :new.podoc;
      
       -- check if the <Date> element exists.
       IF xmldata.existsnode('/PurchaseOrder/Actions/Action/Date') = 1 THEN

         -- Update the <Date> element value to sysdate
         SELECT UPDATEXML(xmldata, '/PurchaseOrder/Actions/Action/Date',
                     '<Date>'|| to_char(sysdate, 'DD-MON-YYYY') || '</Date>')
         INTO tmpxml FROM dual;

         -- Assign the updated XML document to the current record.
         :new.podoc := tmpxml;
       END IF;


    END;
/

• Insert a record into po_xml table.
  
  

  SQL> INSERT INTO po_xml VALUES (1, XMLTYPE('<?xml version="1.0"?> <PurchaseOrder> <Reference>Savitha</Reference> <Actions> <Action> <User>SCOTT</User> <User>ADAMS</User> <Date></Date> </Action> </Actions> </PurchaseOrder>'));

  
  
• Check to see if the record has been inserted into the table in the database with the <Date> element defaulted to sysdate by running the following query:
  

  SQL> SELECT e.podoc.getStringval() FROM po_xml e; E.PODOC.GETSTRINGVAL() --------------------------------------- <PurchaseOrder>  <Reference>Savitha</Reference>   <Actions>    <Action>     <User>SCOTT</User>     <User>ADAMS</User>     <Date>30-MAY-2003</Date>    </Action>   </Actions> </PurchaseOrder>

Note: The XML document to be inserted should have an empty 'Date' element as <Date /> or <Date></Date> for the existsnode() and SELECT statement in the trigger to fire.

Resources

• Oracle9i XML Database Developer's Guide - Oracle XML DB Release 2 (9.2)
  

Summary

This document illustrates how to default an XMLTYPE element in an XML Document to be inserted into an XMLTYPE table or to an XMLType column using a 'before insert' trigger.