| export xml from table data (merged) [message #384190] |
Mon, 02 February 2009 11:35  |
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
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| export xml from table data [message #384625 is a reply to message #384190] |
Wed, 04 February 2009 09:12  |
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 |
 |
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
|
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.
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
