?
Solved

XML file access in  Oracle Forms 6i

Posted on 2003-11-09
1
Medium Priority
?
3,569 Views
Last Modified: 2012-06-21
How can I read XML file in Oracle Forms 6i (without using Java features) - need an examples.


0
Comment
Question by:ipesin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 48

Accepted Solution

by:
schwertner earned 2000 total points
ID: 9714657
If you want only to read the XML file "as is' you can use the Forms/Report package TEXT_IO that reads ASCII flat files.

If this is not your intention then you have to create on the server side a package which will do what you want.


How to Read XML File Stored in Clob Column and Extract Data to Different Table

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;
 
 
Sample Output
-------------
 
SQL> column subject format a20 truncated
SQL> select * from xml_doc;
 
 
DOCID      SUBJECT              TYPE                 CONTENT_TYPE    STATUS      
                          CREATION_DATE    LAST_REVISION_D  LANGUAGE
---------- -------------------- -------------------- --------------- ----------
91739.1    MTS: ORA-29855, DRG  PROBLEM              TEXT/PLAIN      PUBLISHED  
                       14-DEC-1999           15-JUN-2000            USAENG
 

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
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.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

777 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