how to insert XML data using dbms_xmlSAVE

[kanchan_pal]

Hi Tim
I have procedure like this

create or replace procedure insProc(xmlDoc IN CLOB, tableName IN VARCHAR2) is
insCtx DBMS_XMLSave.ctxType;
rows number;
begin
insCtx := DBMS_XMLSave.newContext(tableName); -- get the context handle
rows := DBMS_XMLSave.insertXML(insCtx,xmlDoc); -- this inserts the document
DBMS_XMLSave.closeContext(insCtx); -- this closes the handle
end;

and xml file like this
<?xml version='1.0'?>
<ROWSET>
<ROW num="1">
<EMPNO>7369</EMPNO>
<ENAME>Smith</ENAME>
<JOB>CLERK</JOB>
<MGR>7902</MGR>
<HIREDATE>12/17/1980 0:0:0</HIREDATE>
<SAL>800</SAL>
<DEPTNO>20</DEPTNO>
</ROW>
<!-- additional rows ... -->
</ROWSET>

how to insert this xml file in table

can you give me the steps involved like where i need to keep my xml files
or do i need to create any directory , nedd to give permission or not .
I checked document in this site . but did n't get any help .. and i am new to xml .... so do not know how all this wok .

Thanks & regards
Kanchan

[Tim...]

Hi.

I used your procedure and XML and did this:

Code: Select all

conn scott/tiger

create table emp2 as select * from emp where 1=2;

create or replace procedure insProc(xmlDoc IN CLOB, tableName IN VARCHAR2) is
  insCtx DBMS_XMLSave.ctxType;
  rows number;
begin
  insCtx := DBMS_XMLSave.newContext(tableName); -- get the context handle
  rows := DBMS_XMLSave.insertXML(insCtx,xmlDoc); -- this inserts the document
  DBMS_XMLSave.closeContext(insCtx); -- this closes the handle
end;
/

DECLARE
  l_xml CLOB;
BEGIN
  l_xml := '<?xml version=''1.0''?>
<ROWSET>
<ROW num="1">
<EMPNO>7369</EMPNO>
<ENAME>Smith</ENAME>
<JOB>CLERK</JOB>
<MGR>7902</MGR>
<HIREDATE>12/17/1980 0:0:0</HIREDATE>
<SAL>800</SAL>
<DEPTNO>20</DEPTNO>
</ROW>
</ROWSET>';

  insProc(xmlDoc => l_xml, tableName => 'EMP2');
END;
/

SQL> select * from emp2;

     EMPNO ENAME      JOB              MGR HIREDATE                    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------------- ---------- ---------- ----------
      7369 Smith      CLERK           7902 17-DEC-1980 00:00:00        800                    20

1 row selected.

SQL>

In the anonymous block the XML is assigned like a string to the CLOB, before being passed into the procedure as a parameter. In reality you would probably end up loading this data into the CLOB from a file, like:

Code: Select all

DECLARE
  l_bfile  BFILE := BFILENAME('directory name', 'file name');
  l_xml    CLOB;
BEGIN
  DBMS_LOB.createtemporary (l_xml, TRUE);
 
  DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);
  DBMS_LOB.loadfromfile(l_clob, l_bfile, DBMS_LOB.getlength(l_bfile));
  DBMS_LOB.fileclose(l_bfile);

  insProc(xmlDoc => l_xml, tableName => 'EMP2');
END;
/

As for directories, if you are loading the XML from files you will need to:

- Create a directory, or directories, on the server file system to hold the files. The choice of name, location and structure is up to you. There is no standard.

- You will need an Oracle directory object pointing to the OS directories to enable you to load the files. Once again, the name is up to you.

- The OS directories you use must be accessible by the Oracle user, otherwise the Oracle executable will not be able to access them.

Cheers

Tim...