i have an xml file which i need to parse and insert values into a table for further processing. i have been all over the web for examples and dont know which is best for me. here is example of xml:





d67325e2-5c7b-44bd-948f-7dae6cc81739
2013-01-04T12:46:43
RequestedRecord
Original


89030








1455


JOEY
SMOE5




All



All



2012-01
Fall 2012
Semester


Regular
3.25
C
GARD
300
URBAN GARDENING




2012-06
Summer 2012
Semester


Regular
2.00
F
HIST
220
HISTORY OF TIME




2011-09
Fall 2011
Semester


Regular
1.00
C
ENG
400
WHO WROTE THAT?




2012-03
Spring 2012
Semester


Regular
4.00
A
MATH
260
ALGORITHMS OF NATURE


Regular
2.00
B
PHYS
100
PHYSICS AROUND US








i have tried using the following select statement, which works fine until i get down into the xml. I'm not sure if i am making a silly mistake or if the xml doesnt lend itself to my first try at it. Any help would be greatly appreciated.

SELECT
EXTRACTVALUE (a.COLUMN_VALUE, '/CollegeTranscript/TransmissionData/DocumentID') as Document_id,
EXTRACTVALUE (a.COLUMN_VALUE, '/CollegeTranscript/TransmissionData/CreatedDateTime') as File_date,
EXTRACTVALUE (a.COLUMN_VALUE, '/CollegeTranscript/TransmissionData/DocumentTypeCode') as Doc_type,
EXTRACTVALUE (a.COLUMN_VALUE, '/CollegeTranscript/TransmissionData/TransmissionType') as Transmission_Type,
EXTRACTVALUE (b.COLUMN_VALUE, '/Source/Organization/ATP') as Fice_Code,
EXTRACTVALUE (c.COLUMN_VALUE, '/Destination/Organization') as Destination,
EXTRACTVALUE (d.COLUMN_VALUE, '/Person/SchoolAssignedPersonID') as Student_ID,
EXTRACTVALUE (d.COLUMN_VALUE, '/Person/Name/FirstName') as Student_First_Name,
EXTRACTVALUE (d.COLUMN_VALUE, '/Person/Name/LastName') as Student_Last_Name,
EXTRACTVALUE (e.COLUMN_VALUE, '/AcademicRecord/AcademicSummary/AcademicSummaryType') as Academic_Summary_Type,
EXTRACTVALUE (e.COLUMN_VALUE, '/AcademicRecord/AcademicSummary/UserDefinedExtensions') as User_Defined_Extensions,
EXTRACTVALUE (e.COLUMN_VALUE, '/AcademicRecord/AcademicAward/AcademicAwardLevel') as Academic_Award_Level,
EXTRACTVALUE (f.COLUMN_VALUE, '/AcademicSession/AcademicSessionDetail/SessionDesignator') as Session_Designator,
EXTRACTVALUE (f.COLUMN_VALUE, '/AcademicSession/AcademicSessionDetail/SessionName') as Session_Name,
EXTRACTVALUE (f.COLUMN_VALUE, '/AcademicSession/AcademicSessionDetail/SessionType') as Session_Type,
EXTRACTVALUE (g.COLUMN_VALUE, '/Course/CourseCreditBasis') as Course_Credit_Basis,
EXTRACTVALUE (g.COLUMN_VALUE, '/Course/CourseCreditValue') as Course_Credit_Value,
EXTRACTVALUE (g.COLUMN_VALUE, '/Course/CourseAcademicGrade') as Course_Grade,
EXTRACTVALUE (g.COLUMN_VALUE, '/Course/CourseSubjectAbbreviation') as Course_Subject
FROM TABLE
(XMLSEQUENCE
(EXTRACT
(XMLTYPE
(BFILENAME ('ADFEED', 'sample.xml'),
NLS_CHARSET_ID ('WE8ISO8859P1')),
'/Envelope/CollegeTranscript'))) a,
TABLE (XMLSEQUENCE (EXTRACT (a.COLUMN_VALUE, '/CollegeTranscript/TransmissionData/Source'))) b,
TABLE (XMLSEQUENCE (EXTRACT (a.COLUMN_VALUE, '/CollegeTranscript/TransmissionData/Destination'))) c,
TABLE (XMLSEQUENCE (EXTRACT (a.COLUMN_VALUE, '/CollegeTranscript/Student/Person'))) d,
TABLE (XMLSEQUENCE (EXTRACT (a.COLUMN_VALUE, '/CollegeTranscript/Student/AcademicRecord'))) e,
TABLE (XMLSEQUENCE (EXTRACT (a.COLUMN_VALUE, '/CollegeTranscript/Student/AcademicRecord/AcademicSession'))) f,
TABLE (XMLSEQUENCE (EXTRACT (a.COLUMN_VALUE, '/CollegeTranscript/Student/AcademicRecord/AcademicSession/Course'))) g