XML file access in Oracle Forms 6i

How can I read XML file in Oracle Forms 6i (without using Java features) - need an examples.


ipesinAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

schwertnerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.