Home » Developer & Programmer » JDeveloper, Java & XML » export xml from table data (merged)
export xml from table data (merged) [message #384190] Mon, 02 February 2009 11:35 Go to next message
RAY_HT
Messages: 155
Registered: May 2005
Location: Giza
Senior Member
hi all

what is the best way to export table data to XML file and import data from XML file into database table and query it

Note : i don't mean with import xml into table to save the xml file into CLOB column , i want impot XML file into current table as rows .

what is the best way to do that ?? is it DBMS_XML or something else??

thanks
Re: export to XML files [message #384191 is a reply to message #384190] Mon, 02 February 2009 11:37 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
When all else fails, you can & should RTFM
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14252/toc.htm
Re: export to XML files [message #384192 is a reply to message #384190] Mon, 02 February 2009 11:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe you didn't notice there is a "JDeveloper, Java & XML" forum which contains many examples.

Regards
Michel
Re: export to XML files [message #384193 is a reply to message #384191] Mon, 02 February 2009 11:48 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Good FAQ here
http://forums.oracle.com/forums/thread.jspa?threadID=410714&tstart=0

Re: export to XML files [message #384199 is a reply to message #384190] Mon, 02 February 2009 12:30 Go to previous messageGo to next message
RAY_HT
Messages: 155
Registered: May 2005
Location: Giza
Senior Member
i don't search in java forum as i need it to be pl/sql procedure to call it to export XML or import it into table
Re: export to XML files [message #384200 is a reply to message #384199] Mon, 02 February 2009 12:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The forum is named "JDeveloper, Java & XML", this does not mean that all posts are about all subjects but any of them.

Regards
Michel
export xml from table data [message #384625 is a reply to message #384190] Wed, 04 February 2009 09:12 Go to previous messageGo to next message
RAY_HT
Messages: 155
Registered: May 2005
Location: Giza
Senior Member
dear all

i wrote this procedure to export xml from table data

PROCEDURE exml(p_dir in varchar2 , p_filename in varchar2)
IS
doc DBMS_XMLDOM.domdocument;
xt XMLTYPE;
cur INTEGER := dbms_sql.open_cursor;


v_ignore integer;
BEGIN

DBMS_SQL.PARSE (
cur,'SELECT SYS_XMLAGG (XMLELEMENT ("ROW",
XMLFOREST (a.ID,
a.billingrun_id,
file_no
)
)
)
FROM bl_billing a
WHERE section_code = 1 AND branch_code = 21',DBMS_SQL.v7);


v_ignore := DBMS_SQL.EXECUTE(cur);
LOOP
xt:=dbms_sql.fetch_rows(cur);


EXIT WHEN xt is null;
doc := DBMS_XMLDOM.newdomdocument (xt);
DBMS_XMLDOM.writetofile (doc, p_dir||'/'||p_filename);
END LOOP;

dbms_sql.close_cursor(cur);
EXCEPTION
WHEN OTHERS
THEN
RAISE;
END exml;
but when i try to compile the code , error raised :
PLS-00382: expression is of wrong type

the error at the line in bold , the fetch line

any help?

thanks
Re: export xml from table data [message #384637 is a reply to message #384625] Wed, 04 February 2009 10:23 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From a previous post:
BlackSwan wrote on Wed, 28 January 2009 22:09
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.


Format your post: read "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags.
Use the "Preview Message" button to verify.

Quote:
DBMS_SQL.v7

Why v7?

Use SQL*Plus and copy and paste your session.

What is the return value of dbms_sql.fetch_rows, in the documentation?

Regards
Michel
Previous Topic: pass empty java array to associative array in plsql
Next Topic: Strange Problem connecting Oracle
Goto Forum:
  


Current Time: Thu Mar 28 06:24:22 CDT 2024