Inserting XML data into an Oracle Table

Oracle has a package DBMS_XMLsave that can take an XML document and insert it into a normal relational table. The format for the XML document is

<ROWSET>
  <ROW>
    <COLUMNNAME>
      value
    </COLUMNNAME>
  </ROW>
</ROWSET>

The other argument to the save function is the table name. Two gotchas: 1) by default, the element names are case sensitive. I think this is a nightmare with table column names, so luckily there is a flag to disable case sensitivity (why do developer's inflict case sensitivity on each other?) 2) The format is very barebones. Attributes are ignored and you can't use functions within the values for columns. You can only insert into one table as well. I've created some code to get around this, which is the topic of another post.

At least, though, you CAN use XSLT to transform your document to something that Oracle can handle natively.

Here is a stored procedure that inserts such an XML document:

CREATE OR REPLACE PROCEDURE InsertXML (p_xml in xmltype, p_tablename in varchar2) is

   queryCtx DBMS_XMLsave.ctxType;

   rowsInserted INT;

BEGIN

   --Defines the query

   queryCtx := SYS.DBMS_XMLSave.newContext(p_tablename);
  
   DBMS_XMLSave.setIgnoreCase(queryCtx, 1);
  
   rowsInserted := DBMS_XMLSave.insertXML(queryCtx,p_xml.getCLOBval());
  
   DBMS_XMLSave.closeContext(queryCtx);
  
   commit;

END;