Home » Developer & Programmer » JDeveloper, Java & XML » Reading XML file
Reading XML file [message #208566] Mon, 11 December 2006 06:08 Go to next message
bhoite_amol83
Messages: 110
Registered: June 2005
Location: Pune
Senior Member
Hi,

I need to read XML page from Oracle.

Can anyone tell me how to read xml page from Oracle pl/sql.
or by which way i can read xml pages?

Please give me a simple example to read XML page and store data into the database.

Thnaks in advance any kind of help.

Amol.
Re: Reading XML file [message #208906 is a reply to message #208566] Tue, 12 December 2006 09:33 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Read an XML page from where?
A file on disk?
A web page?
Re: Reading XML file [message #210753 is a reply to message #208566] Fri, 22 December 2006 03:40 Go to previous messageGo to next message
bhoite_amol83
Messages: 110
Registered: June 2005
Location: Pune
Senior Member
Thanks for your reply,
I have a xml file at my disk location
e.g c:\test.xml file has information which need to be upload in oracle table data.

for this purpose i need to read XML file.
Please give me a simple example to read a XML from Oracle PL/sql coding.

Thanks in advance for any type of help.

Amol
Re: Reading XML file [message #211517 is a reply to message #208566] Fri, 29 December 2006 01:47 Go to previous messageGo to next message
hobbes
Messages: 173
Registered: January 2006
Senior Member
You could do this:
1. Read the file into a CLOB variable
2. Convert CLOB to XMLTYPE
3. Use standard XML extract functions to store the data in the database

Note that the directory in which the XML file is placed must be on the server (not the client), and Oracle must have read/write privileges on it.

Example (Warning: Untested!):
-- run as system
CREATE DIRECTORY UTL_DIR AS <dir in which XML file is placed>;
GRANT READ ON DIRECTORY UTL_DIR TO <dbuser>; 

-- run as <dbuser>
DECLARE
  v_file      utl_file.file_type;
  v_dir       VARCHAR2(120) := 'UTL_DIR';
  v_filename  VARCHAR2(100) := 'datafile.xml';
  c           CLOB := NULL;
  x           XMLTYPE;
  str         VARCHAR2(1000);
BEGIN
  -- Read the file into CLOB variable
  v_file := utl_file.fopen('UTL_DIR', v_filename, 'r');
  WHILE (TRUE) LOOP
    BEGIN
    utl_file.get_line(v_file, str);
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        utl_file.fclose(v_file);
        EXIT;
    END;
    c := c||str;
  END LOOP;

  -- Cast CLOB data as XMLTYPE instance
  x  := XMLTYPE(c);

  -- Process the XMLTYPE instance
  -- ...

END;
/

[Updated on: Fri, 29 December 2006 01:47]

Report message to a moderator

Re: Reading XML file [message #214271 is a reply to message #211517] Mon, 15 January 2007 11:03 Go to previous message
Balgarvie
Messages: 4
Registered: September 2006
Junior Member
I wonder if I might butt in here. I'm doing virtually exactly the procedure discussed in this thread, and it works. However, I find that in order for my changes to be visible in the client I have to restart the server used to host the client app.
This is the only time I ever have to do such a restart, changes to "normal" textual data are seen immediately on commit, is there any reason why this should be ?
Previous Topic: Passing array of objects from java to oracle.
Next Topic: How to open excel
Goto Forum:
  


Current Time: Thu Mar 28 20:56:07 CDT 2024