Extending DBMS.XMLSave to Insert into Multiple Tables

Oracle, through the DBMS.XMLSave package, is able to take XML in a specific format and insert it into normal relational tables. This package has two major limitations in my opinion: 1) The values of rows are NOT evaluated, so functions do not work and sequences don't work. 2) Only one target table can be specified.

I've written a procedure which wraps Oracle's format inside some higher level XML elements to allow insertion into multiple tables. My solution for sequences and functions is not very flexible, but it will work for specific applications, and I can make some recommendations on how to improve the flexibility.

Let's start with inserting into multiple tables. If you look at my previous post, you will see Oracle's format for the DBMS.XMLSave function. I wrap it in two higher level elements, records (the new root) and the record element which contains a rowset.

<records>
  <record table="tablenamegoeshere">
    <rowset>
      <row>
        <columnname>value</columnname>
      </row>
    </rowset>
  </record>
<records>

A stored procedure to handle these XML files must loop through each record and call Oracle's XMLSave on the rowset contained within. Each record element has an attribute, table, which specifies which table to insert into.

The next step is to implement sequences. We could take a cue from Hibernate and add an attribute to the column that uses a sequence. I didn't have time to implement this, so what I've done is update one column in each row with the results of an execute immediate. This assumes that there is only one sequence that needs updating and that it's name & and the containing column's name is based on the table attribute in the record element.

I use a similar procedure for function calls-- use XPath to update columns. The structure of the data is very rigid unfortunately because of this.

One gotcha -- make sure rowset and row elements are LOWER CASE. Oracle's procedures are case sensitive.

create or replace procedure PARSE_RECORD_XML(p_xml in xmltype, p_xmlid in integer) is
   v_i            INTEGER;
   v_j            INTEGER;
   v_func_arg    INTEGER;
   v_seqnum       INTEGER;
   v_func_result          VARCHAR2(50);
   v_xmlfrag      XMLTYPE;
   v_tablename    VARCHAR2(50);
   v_seqname      VARCHAR2(100);
   v_maxi         INTEGER;
   v_maxj         INTEGER;
  
begin
  select count(*) into v_maxi from TABLE(XMLSequence(extract(p_xml,'/records/record')));               
  FOR v_i IN 0..v_maxi-1 LOOP
      v_xmlfrag := p_xml.extract('/records/record[count(preceding-sibling::*)='||v_i||']');
      v_tablename := v_xmlfrag.extract('/record/@table').getStringVal();
      v_seqname := v_tablename || 'ID'; -- sequence name is a concatenation of table attribute and ID
     
      select count(*) into v_maxj from TABLE(XMLSequence(extract(v_xmlfrag,'/record/rowset/row')));               
 
      FOR v_j in 0..v_maxj-1 LOOP
        -- Get sequence number
        execute immediate 'SELECT ' || v_seqname || '.nextval from dual' into v_seqnum;
       
        -- Execute a function on some data
        v_func_result := v_xmlfrag.extract('/record/rowset/row[count(preceding-sibling::*)='||v_j||']/func_arg/text()').getStringVal();
       
        -- Insert the parent id
        select insertchildxml(v_xmlfrag, '/record/rowset/row[count(preceding-sibling::*)='||v_j||']', 'parentid',
                    XMLType('' || p_xmlid || '')) into v_xmlfrag from dual;
       
        -- Insert function result           
        select insertchildxml(v_xmlfrag, '/record/rowset/row[count(preceding-sibling::*)='||v_j||']', 'func_result',
                    XMLType('' || getMRN(v_patientid) || '')) into v_xmlfrag from dual;

        -- Insert sequence id
        select insertchildxml(v_xmlfrag, '/record/rowset/row[count(preceding-sibling::*)='||v_j||']', lower(v_seqname),
                    XMLType('<' || lower(v_seqname) || '>' || v_seqnum || '')) into v_xmlfrag from dual;
             
       
      END LOOP;
      InsertXML(v_xmlfrag.extract('/record/rowset'), v_tablename);
  END LOOP;
end PARSE_RECORD_XML;