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
