Oracle XML Data Export To Text File
Outlined below is a way of extracting data from an Oracle database in XML format. After extracting the data it is exported to a text file.
Extracting the data into XML format is done using the following Oracle commands XMLElement, XMLForest, XMLAGG.
To export the data to text file a simple spool command is used.
Here is a sample script on how to extract Oracle database data into XML format and export to text file.
set long 10000000
spool c:\\StudentXMLJan08.xml
SELECT
XMLElement("Student",
XMLForest(s.studentid "studentid",
s.firstname "firstname",
s.lastname "surname"),
XMLElement("enrollments",
(SELECT XMLAGG(
XMLForest(sc.coursecode "courseid"))
FROM studentcourse sc
WHERE sc.studentid = s.studentid
and sc.is_approved='Y'
and sc.takenyear='2008'
and sc.takenterm='1')))
FROM student s
where s.statuscode in (select studentstatuscode from studentstatus where studentstatusactive=1)
order by s.studentid;
spool off
The above script could be tweaked (fields, tables, conditions) to suite your particular case. I hope you find it useful.
Posted
on
Tuesday, January 29th, 2008 at 6:59 am under
oh, my golly! how on earth would i ever understand this! it’s not a language that everyone speaks
any further add ons on this?
January 30th, 2008 at 10:42 amOkay - understand the purpose of the set long parameter but …
I am not able to output more than the first 2 elements - and they appear on the same line !
Help appreciated
from a bash script
sqlplus myuser/mypass@mydb myoutput.xml
June 30th, 2008 at 10:34 pmset pages 0 head off lines 1200 echo off feed off long 10000000
SELECT XMLElement(“ElementSet”
XMLForest( mygroup_1 as “MyGroup_1”,
mycol01 as “mycol01”,
mycol02 as “mycol02”,
mycol03 as “mycol03”,
mycol04 as “mycol04”,
mycol05 as “mycol05”,
mycol06 as “mycol06”,
mycol07 as “mycol07”,
mycol08 as “mycol08”,
mycol09 as “mycol09”,
mycol10 as “mycol10”,
mycol11 as “mycol11”,
mycol12 as “mycol12”,
mycol13 as “mycol13”,
mycol14 as “mycol14”,
mycol15 as “mycol15”,
mycol16 as “mycol16”,
mycol17 as “mycol17”,
mycol18 as “mycol18”,
mycol19 as “mycol19”) ) “Forest”
FROM mytable;
there’s a missing comma after
SELECT XMLElement(“ElementSet”
this should be
SELECT XMLElement(“ElementSet”,
i hope that corrects it.
July 1st, 2008 at 10:19 pm