Re: Help with PL/SQL loading XML data

From: Mathew <sheemat_at_yahoo.com>
Date: Sat, 21 Jul 2001 21:53:13 GMT
Message-ID: <23881c5e.0106150703.28b0efaa_at_posting.google.com>

allenh_at_starbase.neosoft.com (Allen) wrote in message news:<4F14B519369D48EA.6A2ECF4728644591.537271C9985A99BD_at_lp.airnews.net>...
> Oracle 8.1.7, sun solaris. I need to write a PL/SQL stored procedure that
> will load an XML datafile off a drive and put it into some type of BLOB or
> long column (u tell me), then parse that data into a table.
>
> Is there some example code out there that will do this that I can
> reference? Just straight PL/SQL, no java at this time please.
>
> Any help much appreciated!
>
> thx.. Allen

Refer to the book Oracle XML by Oreilly (or download the code from the Oreilly website)

For example the following , if you create a directory in oracle with the XMLfile will load it into a clob...

CREATE OR REPLACE PROCEDURE insertXmlFile( dir VARCHAR2,

                                          file VARCHAR2,
                                          name VARCHAR2 := NULL) IS

  theBFile BFILE;
  theCLob CLOB;
  theDocName VARCHAR2(200) := NVL(name,file); BEGIN      INSERT INTO xml_documents(docname,xmldoc) VALUES(theDocName,empty_clob())   RETURNING xmldoc INTO theCLob;

  theBFile := BFileName(dir,file);

  dbms_lob.fileOpen(theBFile);

  dbms_lob.loadFromFile(dest_lob => theCLob,
                         src_lob => theBFile,
                         amount  => dbms_lob.getLength(theBFile));

  dbms_lob.fileClose(theBFile);
  COMMIT;
END; Received on Sat Jul 21 2001 - 23:53:13 CEST