dbms_xmlsave and dbms_xmlstore Tips

The dbms_xmlsave  package can be used to upload XML data into a table. It requires a good bit of manual typing as each tag has to be quoted and concatenated. The dbms_xmlstore  package allows uploading an XML file directly and inserting the contents into a table.

Both dbms_xmlsave and dbms_xmlstore are part of the Oracle XML SQL Utility (XSU). The main differences between them are that dbms_xmlstore is written in C and compiled in kernel form, so it is faster; dbms_xmlstore uses Simple API for XML to parse XML, providing higher scalability and consuming less memory than the dbms_xmlsave package. Another important difference between them is that some dbms_xmlstore functions like insertxml, updatexml and deletexml can have XMLType (from the dbms_xmlstore package) instances in addition to CLOB values, thereby offering a better integration with XML DB.

Here is an example of uploading a new record into the EMP table using the dbms_xmlsave package.

conn sys@ora11g as sysdba

Connected to:

Oracle 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

declare

  insctx   dbms_xmlsave.ctxtype;

  n_rows   number;

  s_xml    varchar2 (32767);

begin

  s_xml :=

'<rowset>'

|| '<row>'

||  '<empno>7783</empno>'

||  '<ename>clark</ename>'

||  '<job>manager</job>'

||  '<mgr>7839</mgr>'

||  '<sal>2450</sal>'

||  '<deptno>10</deptno>'

|| '</row>'

||'</rowset>';

insctx := dbms_xmlsave.newcontext ('emp');

-- get the context handle

dbms_xmlsave.setrowtag (insctx, 'row');

n_rows := dbms_xmlsave.insertxml (insctx, s_xml);

-- this inserts the document

dbms_xmlsave.closecontext (insctx);

end;

/

A bit cumbersome, but it can be done.

In this example, table emp3 is a copy of emp. The generated xml data file is named emp3.xml and is located in a directory object named mydir - C:\Temp in this example.  Here is the procedure code to upload an xml file:

conn sys@ora11g as sysdba

Connected to:

Oracle 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

create or replace procedure insertxml

(dirname in varchar2,

 filename in varchar2,

 tablename in varchar2)

is

xmlfile bfile;

myclob clob;

insCtx dbms_xmlstore.ctxtype;

rows number;

begin

dbms_lob.createtemporary(myclob, TRUE, 2);

-- handle to the xml file on the OS

xmlfile := bfilename(upper(dirname),filename);

-- open file

dbms_lob.fileopen(xmlfile);

-- copy contents of file into empty clob

dbms_lob.loadFromFile

(myclob, xmlfile, dbms_lob.getLength(xmlfile));

-- context handle

insCtx := dbms_xmlstore.newcontext(upper(tableName));

-- this inserts the file

rows := dbmd_xmlstore.insertxml(insctx, myclob);

dbms_output.put_line(to_char(rows) || ' rows inserted');

-- close handle

dbms_xmlstore.closecontext(insctx);

end insertxml;

/

The process to upload a file is to execute the procedure and pass in the directory object name, the file name, and the target table.

Since dbms_xmlstore  has advantages over dbms_xmlsave, it is recommended that it be used first. In Oracle 11g, dbms_xmlsave is not installed with XML DB;  dbms_xmlstore is installed by default when installing the XML DB functionality.