This forum is currently locked. You can't register or post questions at this time. (read more)

how to insert XML data using dbms_xmlSAVE

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

kanchan_pal
Senior Member
Posts: 62
Joined: Mon Nov 14, 2005 2:00 am

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

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

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


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 5 guests

cron