how to insert XML data using dbms_xmlSAVE

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

how to insert XML data using dbms_xmlSAVE

Postby kanchan_pal » Mon Nov 14, 2005 8:32 am

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
kanchan_pal
Senior Member
 
Posts: 62
Joined: Mon Nov 14, 2005 2:00 am

Postby Tim... » Mon Nov 14, 2005 11:32 am

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17933
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle SQL and PL/SQL Development

Who is online

Users browsing this forum: No registered users and 1 guest

cron