The best way to insert a XML input to an oracle table in stored procedure

oracleperformancestored-proceduresxml

I want to write a stored procedure which has a XML input (clob type) and inserts the data to a table. my XML structure is something like this:

<rowset>
    <row>
        <name>tom</name>
        <family>mack</family>
        ....
    </row> 
    <row>
        <name>marry</name>
        <family>skot</family>
        ....
    </row> 
    ...
</rowset>

I have just stock in many options that oracle gave me to handle this. using extractvalue and XMLSequence– using DBMS_XMLDOM– using DBMS_XMLStore or many other options.

I want to know which one is better according to the performance and resources that use. the input XML is usually less than 50KB.

Best Answer

You can query your XML data directly in SQL with XMLTABLE:

SQL> VARIABLE xml VARCHAR2(4000);
SQL> BEGIN :xml :=
  2  '<rowset>
  3       <row>
  4           <name>tom</name>
  5           <family>mack</family>
  6       </row>
  7       <row>
  8           <name>marry</name>
  9           <family>skot</family>
 10       </row>
 11   </rowset>';
 12  END;
 13  /

PL/SQL procedure successfully completed

SQL> SELECT x.*
  2    FROM XMLTable('for $i in /rowset/row return $i'
  3                    passing XMLTYPE(:xml) columns
  4                       name VARCHAR2(200) path 'name',
  5                       family VARCHAR2(200) path 'family'
  6                  ) x;

NAME    FAMILY
------- -------
tom     mack
marry   skot

You can also use XMLSequence and EXTRACT (see this SO question for example) but as noted in the documentation these functions are deprectated.

Another option is DBMS_XMLSTORE, although you can't really customize it much (needs perfect match between input xml tags and output column names):

SQL> CREATE TABLE my_table ("name" VARCHAR2(10), "family" VARCHAR2(10));

Table created

SQL> DECLARE
  2     l_ctx  dbms_xmlstore.ctxtype;
  3     l_rows NUMBER;
  4  BEGIN
  5     l_ctx := dbms_xmlstore.newcontext('MY_TABLE'); -- Get saved context
  6     dbms_xmlstore.clearUpdateColumnList(l_ctx); -- Clear the update settings
  7  
  8     -- Set the columns to be updated/inserted as a list of values
  9     dbms_xmlstore.setUpdateColumn(l_ctx, 'name');
 10     dbms_xmlstore.setUpdateColumn(l_ctx, 'family');
 11     -- Set ROW tag
 12     dbms_xmlstore.setRowTag(l_ctx, 'row');
 13  
 14    -- Insert the doc.
 15    l_rows := DBMS_XMLSTORE.insertXML(l_ctx, :xml);
 16    DBMS_OUTPUT.put_line(l_rows || ' rows inserted.');
 17  
 18    -- Close the context
 19    DBMS_XMLSTORE.closeContext(l_ctx);
 20  
 21  END;
 22  /

2 rows inserted.

PL/SQL procedure successfully completed

SQL> select * from my_table;

name       family
---------- ----------
tom        mack
marry      skot

I'm not sure performance-wise which option is faster, this certainly depends on the data -- you'll have to benchmark.

Related Question