Solved

XML file load into Oracle table

Posted on 2004-03-25
8
18,549 Views
Last Modified: 2009-12-04
Greetings,

I found the following example on how to load XMl file into Oracle table from the experts  site.
However, when I tried to replicate this example in my environment it didn't work and few errors were identified.
Here is my questions:
1. is this still the simplest solution for loading xml file into Oracle
2. Please respond to the errors I found using these scripts:
a). -- get a handle to the xml file on the OS
  xmlfile := Bfilename(dirname,filename); I used utl_file_dir instead of "CREATE DIRECTORY XML_DIR AS 'c:\ash';" as it was suggested
As a result
next statement "DBMS_LOB.fileOpen(xmlfile); " has the following  exception
ORA-22285: non-existent directory or file for FILEOPEN operation
b).   I had a compile error for the following statement
xmlgen.resetOptions;
error - PLS-00201: identifier 'XMLGEN.RESETOPTIONS' must be declared
3. are there any other examples I can have for load xml file into Oracle table ?

Following is the example I used:


  -- open the file  
  DBMS_LOB.fileOpen(xmlfile);


Pre-requisites
--------------
 
- Oracle RDBMS version is 8.1.6 or higher.
- Oracle JServer is installed.
- XML-SQL (XSU) Utility is installed.
 
XML file ( example.xml )
------------------------
 
The <ROWSET> tag specifies the default tag name for the document.
The <ROW> tag specifies the default tag name for the ROW elements.
 
- - - - - - - - - - - - - - - - File begins here - - - - - - - - - - - - - - - -
<ROWSET>
<ROW>
<DOCID> 91739.1 </DOCID>
<SUBJECT> MTS: ORA-29855, DRG-50704, ORA-12154: on create index using Intermedia </SUBJECT>
<TYPE> PROBLEM </TYPE>
<CONTENT_TYPE> TEXT/PLAIN </CONTENT_TYPE>
<STATUS> PUBLISHED </STATUS>
<CREATION_DATE> 14-DEC-1999 </CREATION_DATE>
<LAST_REVISION_DATE> 05-JUN-2000 </LAST_REVISION_DATE>
<LANGUAGE> USAENG </LANGUAGE>
</ROW>
</ROWSET>
- - - - - - - - - - - - - - - - File ends here - - - - - - - - - - - - - - - -
 
How to Read XML File Stored in Clob Column and Extract Data to Different Table
------------------------------------------------------------------------------
 
1. Create directory object mapped to physical directory which contain the XML file.
Note: Oracle user should have (at least) read permission for the directory and the XML file.
 
CREATE DIRECTORY XML_DIR AS 'c:\ash';
 
2. Create a table containing a CLOB and insert a row for the XML file.
XML_CLOB stores the xml document in a clob column,  
 
CREATE TABLE xml_clob(
docname    VARCHAR2(100) PRIMARY KEY,
doc        CLOB,
insertdate DATE DEFAULT SYSDATE);
 
3. Create PL/SQL procedure to insert the example.xml file into the  
database table xml_clob.  
 
CREATE OR REPLACE PROCEDURE insertXML (dirname IN VARCHAR2,  
                                      filename IN VARCHAR2)
IS
  xmlfile BFILE;
  myclob  CLOB;
BEGIN
  INSERT INTO xml_clob (docname, doc)  
  VALUES (filename, empty_clob())
  RETURNING doc into myclob;  
   
  -- get a handle to the xml file on the OS
  xmlfile := Bfilename(dirname,filename);
 
  -- open the file  
  DBMS_LOB.fileOpen(xmlfile);
 
  -- copy the contents of the file into the empty clob
  DBMS_LOB.loadFromFile(myclob, xmlfile, dbms_lob.getLength(xmlfile));
 
END insertXML;
 
4. Create a table into which the XML document data has to be loaded after reading it from  
the XML_CLOB table. Note that the column names of the table should match the XML tags.
 
CREATE TABLE XML_DOC (
DOCID         VARCHAR2(10),
SUBJECT       VARCHAR2(100),
TYPE          VARCHAR2(20),
CONTENT_TYPE  VARCHAR2(20),
STATUS        VARCHAR2(20),
CREATION_DATE VARCHAR2(15),
LAST_REVISION_DATE VARCHAR2(15),
LANGUAGE      VARCHAR2(10)
);
 
 
5. Create procedure to read XML stored in clob column of the xml_clob table, and
load extracted data into XML_DOC table.  
 
 
CREATE OR REPLACE PROCEDURE loadxml1 AS
  fil    clob;
  buffer varchar2(1000);
  len    INTEGER;
  insrow INTEGER;
BEGIN
  SELECT doc INTO fil FROM xml_clob WHERE docname='example.xml';
 
  len := DBMS_LOB.GETLENGTH(fil);
  DBMS_OUTPUT.PUT_LINE('length '||len);
 
  DBMS_LOB.READ(fil,len,1,buffer);
  xmlgen.resetOptions;
  insrow := xmlgen.insertXML('xml_doc',buffer);
  DBMS_OUTPUT.PUT_LINE('length ins '||insrow);
 
 EXCEPTION
  WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('In Exception');
  DBMS_OUTPUT.PUT_LINE(SQLERRM(SQLCODE));
 
end;
 
0
Comment
Question by:smena
  • 5
  • 2
8 Comments
 
LVL 47

Expert Comment

by:schwertner
ID: 10686101
On Oracle 9i:

THE REAL RESULT:


SQL*Plus: Release 9.2.0.4.0 - Production on Tue Jan 13 14:56:19 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> connect xml/xml@test

SQL> CREATE TABLE empleados (empid   NUMBER PRIMARY KEY,
  2                          empname VARCHAR2(30),
  3                          empjob  VARCHAR2(30),
  4                          empsal  NUMBER);

Table created.

SQL> create or replace procedure InsertXML(xmlDoc IN VARCHAR2, tableName IN VARCHAR2) is
  2     insCtx DBMS_XMLSave.ctxType;
  3     rows number;
  4   begin
  5      insCtx := DBMS_XMLSave.newContext(tableName); -- get the context handle
  6      rows := DBMS_XMLSave.insertXML(insCtx,xmlDoc); -- this inserts the document
  7      dbms_output.put_line(to_char(rows) || ' rows inserted');
  8      DBMS_XMLSave.closeContext(insCtx);            -- this closes the handle
  9  end;
 10  /

Procedure created.
SQL> exec InsertXML('<?xml version="1.0"?><ROWSET><ROW num="1"><EMPID>10</EMPID><EMPNAME>Perry Smith
</EMPNAME><EMPJOB>Manager</EMPJOB><EMPSAL>800</EMPSAL></ROW><ROW num="1"><EMPID>20</EMPID><EMPNAME>J
ohn Calvach</EMPNAME><EMPJOB>Principal Support Consultant</EMPJOB><EMPSAL>900</EMPSAL></ROW><ROW num
="1"><EMPID>30</EMPID><EMPNAME>Louis Bald</EMPNAME><EMPJOB>Technical Specialist</EMPJOB><EMPSAL>400<
/EMPSAL></ROW><ROW num="1"><EMPID>40</EMPID><EMPNAME>Anthony Flowers</EMPNAME><EMPJOB>Technical Team
 Leader</EMPJOB><EMPSAL>500</EMPSAL></ROW><ROW num="1"><EMPID>50</EMPID><EMPNAME>George Monk</EMPNAM
E><EMPJOB>Support Consultant</EMPJOB><EMPSAL>200</EMPSAL></ROW></ROWSET>','empleados');

PL/SQL procedure successfully completed.

SQL> select * from empleados;

     EMPID EMPNAME                        EMPJOB
---------- ------------------------------ ------------------------------
    EMPSAL
----------
        10 Perry Smith                    Manager
       800

        20 John Calvach                   Principal Support Consultant
       900

        30 Louis Bald                     Technical Specialist
       400


     EMPID EMPNAME                        EMPJOB
---------- ------------------------------ ------------------------------
    EMPSAL
----------
        40 Anthony Flowers                Technical Team Leader
       500

        50 George Monk                    Support Consultant
       200


SQL>



SQL> select SYS_XMLGEN(empid) FROM empleados;

SYS_XMLGEN(EMPID)
---------------------------------------------------------
<?xml version="1.0"?>
<EMPID>10</EMPID>

<?xml version="1.0"?>
<EMPID>20</EMPID>

<?xml version="1.0"?>
<EMPID>30</EMPID>

<?xml version="1.0"?>
<EMPID>40</EMPID>

SYS_XMLGEN(EMPID)

<?xml version="1.0"?>
<EMPID>50</EMPID>


SQL> select SYS_XMLAGG(SYS_XMLGEN(empid)) FROM empleados;
<?xml version="1.0"?>
<ROWSET>
<EMPID>10</EMPID>
<EMPID>20</EMPID>
<EMPID>30</EM

0
 
LVL 8

Expert Comment

by:baonguyen1
ID: 10686102
I have post in other thread you ask:

http://www.experts-exchange.com/Databases/Oracle/Q_20932467.html

Hope this helps
0
 

Author Comment

by:smena
ID: 10689237
Are there any way to use "core" PL_SQL functional  without using XDK?

I have a very simple XML file format that just needs to be loaded into one oracle table....

I wasn;t able to execute "exec InsertXML()" procedure. Can file contants can be referenced as a CLOB.
My invision is to have to step process:
1. Load file contantst from XML into one table where XML string defined as CLOB
2. Use some of the existing pl sql utilities to parse data into the second table where data is parsed into the specisifed columns
0
 

Author Comment

by:smena
ID: 10689475
schwertner,

can you make your example more explicit?
Can you specify what each parapmeter means
xmlDoc - file contents?
tableName - Does it mean that "empleados"

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 47

Expert Comment

by:schwertner
ID: 10694111
SQL> exec InsertXML.....
means invocation of the procedure InsertXML


('<?xml version="1.0"?><ROWSET><ROW .....',
means the character string representing the XML document


,'empleados');

means the name of the Oracle table where the columns of the XML will be stored.
This table is just created using SQL*Plus:

SQL> CREATE TABLE empleados (empid   NUMBER PRIMARY KEY,
  2                          empname VARCHAR2(30),
  3                          empjob  VARCHAR2(30),
  4                          empsal  NUMBER);
 
0
 
LVL 47

Expert Comment

by:schwertner
ID: 10694118
If you download the Oracle 9i docs from OTN in the XML stuff you will be able to read the detail explanation of the used packages.
0
 
LVL 47

Expert Comment

by:schwertner
ID: 10694125
It is possible to use instead string CLOB. But you have to find the example in the mentioned docs.
0
 
LVL 47

Accepted Solution

by:
schwertner earned 250 total points
ID: 10694129
i have an example to load the XML as text in XML table, but this example is in the office computer. i will be able if you need it to send it to you on Monday. but you can also use search machines on the WEB - there are enough sources.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
levels for reporting 5 65
Oracle RMAN Database Restore 5 52
oracle report printing 2 pages in one page 2 58
Oracle Listener Not Starting 11 27
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now