select * from V$VERSION
BANNER
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Solaris: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
Hi, I am totally new to the forum, and to XML, and more importantly,
totally new to using Oracle SQL to select XML formatted content that I have loaded into
an Oracle table defined as XMLTYPE. I'm okay with PL-SQL
and stored procedures, where I will ultimately do this processing
once I get a handle on XML DB querying from an XMLTYPE table.
I apologize if I am posting this question to the wrong oracle
XML forum. Please advise if so.
Based on reading the oracle documentation about XML DB,
and more importantly, reading dozens of posts to this forum,
I have been successful in loading the XML files
into a SQL XMLTYPE table and doing simple queries against that table
to retrieve some of its data so that I can then
insert that data into other target oracle
relational tables. Thanks to your excellent explanations
in this forum, I am getting there as a newbie.
I need guidance on a common question
to the forum, i.e., how to select parent/child data
from XMLTYPE tables. I am able to follow the forum examples
and can replicate the methods shown on the many example XML contents
shown on this forum, but not
against the XML that I have to process.
I am wondering if my struggle
is caused by my lack of knowledge, or by ill-formed
XML content supplied to me by the educational vendor.
The XML content has structured the XML content nodes in such a way
that I do not seem to be able to apply the parent/child sql methods
I have been able to use for other XML examples I have tested against.
I hope that this post can help me, and any others who are attempting
to select parent/child data.
So, here goes. My XML file shown below represents High School Transcript
data, for which I need to be able to parse out into my own oracle relational
tables for that student, his personal info, and his course info, etc.
i.e., for our example, which courses he has taken for which High School grade levels.
The vendor-supplied XML seems to put the Courses and the High School
grade level in "parallel nodes," instead of parent/child nodes, so I am
struggling to be able to use SQL to differentiate which course the student
took in NinthGrade versus TenthGrade.
-- WHat I would like to determine from a select statement:
LASTNAME GradeLevel COURSETITLE
=============================
Smith NinthGrade PHYS ED 101
Smith TenthGrade CALCULUS 201
Smith TenthGrade ZOOLOGY 202
(The data has been simpliied and masked, but is true to the content and
is queryable).
select * from V$VERSION
BANNER
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Solaris: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
drop table TRANSCRIPT
create table TRANSCRIPT (
CONTENT xmltype
)
xmltype column CONTENT store as securefile binary xml
;
insert into TRANSCRIPT values
('<?xml version="1.0" encoding="UTF-8"?>
<arb:AcademicRecordBatch xmlns:arb="urn:org:pesc:message:AcademicRecordBatch:v1.0.0">
<HSTrn:HighSchoolTranscript xmlns:HSTrn="urn:org:pesc:message:HighSchoolTranscript:v1.0.0">
<TransmissionData>
<DocumentID>2013-01-02T09:06:15|D123456789</DocumentID>
</TransmissionData>
<Student>
<Person>
<Name>
<FirstName>John</FirstName>
<LastName>Doe</LastName>
</Name>
</Person>
<AcademicRecord>
<AcademicSession>
<AcademicSessionDetail>
<SessionBeginDate>2001-08-31</SessionBeginDate>
<SessionEndDate>2001-12-31</SessionEndDate>
</AcademicSessionDetail>
<StudentLevel>
<StudentLevelCode>NinthGrade</StudentLevelCode>
</StudentLevel>
<Course>
<CourseTitle>KEYBOARD 101</CourseTitle>
</Course>
<Course>
<CourseTitle>SCIENCE 101</CourseTitle>
</Course>
</AcademicSession>
<AcademicSession>
<AcademicSessionDetail>
<SessionBeginDate>2002-08-31</SessionBeginDate>
<SessionEndDate>2002-12-31</SessionEndDate>
</AcademicSessionDetail>
<StudentLevel>
<StudentLevelCode>TenthGrade</StudentLevelCode>
</StudentLevel>
<Course>
<CourseTitle>MATH 201</CourseTitle>
</Course>
<Course>
<CourseTitle>SOCIOLOGY 202</CourseTitle>
</Course>
<Course>
<CourseTitle>BIOLOGY 257</CourseTitle>
</Course>
</AcademicSession>
</AcademicRecord>
</Student>
<Student>
<Person>
<Name>
<FirstName>John Q.</FirstName>
<LastName>Smith</LastName>
</Name>
</Person>
<AcademicRecord>
<AcademicSession>
<AcademicSessionDetail>
<SessionBeginDate>2001-08-31</SessionBeginDate>
<SessionEndDate>2001-12-31</SessionEndDate>
</AcademicSessionDetail>
<StudentLevel>
<StudentLevelCode>NinthGrade</StudentLevelCode>
</StudentLevel>
<Course>
<CourseTitle>PHYS ED 101</CourseTitle>
</Course>
</AcademicSession>
<AcademicSession>
<AcademicSessionDetail>
<SessionBeginDate>2002-08-31</SessionBeginDate>
<SessionEndDate>2002-12-31</SessionEndDate>
</AcademicSessionDetail>
<StudentLevel>
<StudentLevelCode>TenthGrade</StudentLevelCode>
</StudentLevel>
<Course>
<CourseTitle>CALCULUS 201</CourseTitle>
</Course>
<Course>
<CourseTitle>ZOOLOGY 202</CourseTitle>
</Course>
</AcademicSession>
</AcademicRecord>
</Student>
</HSTrn:HighSchoolTranscript>
</arb:AcademicRecordBatch>
');
-- works fine and illustrates how to pull out name
SELECT
p.LastName
FROM TRANSCRIPT t
, XMLTable('//Student/Person/Name'
passing t.CONTENT
columns
LastName varchar2(40) path 'LastName'
) P
;
-- this gives a result set, but with incorrect results
SELECT
p.LastName
,s.CourseTitle
FROM
TRANSCRIPT
, XMLTable('//Student/Person/Name'
passing TRANSCRIPT.CONTENT
columns
LastName varchar2(40) path 'LastName'
) P
, XMLTable('//Student/AcademicRecord/AcademicSession/Course[CourseTitle=*]'
passing TRANSCRIPT.content
columns
CourseTitle_rno for ordinality
,CourseTitle varchar2(40) path 'CourseTitle'
) S
where LastName = 'Smith'
;
-- But Smith is not taking all these courses!
LASTNAME COURSETITLE
Smith KEYBOARD 101
Smith SCIENCE 101
Smith MATH 201
Smith SOCIOLOGY 202
Smith BIOLOGY 257
Smith PHYS ED 101
Smith CALCULUS 201
Smith ZOOLOGY 202
-- WHat I would like to determine from a select statement:
LASTNAME GradeLevel COURSETITLE
==========================
Smith NinthGrade PHYS ED 101
Smith TenthGrade CALCULUS 201
Smith TenthGrade ZOOLOGY 202
-- One of many things I tried that yields no output:
SELECT
xt.LastName
,xt2.course_rno
,xt3.CourseTitle
FROM TRANSCRIPT,
XMLTable('//Student/Person/Name'
PASSING TRANSCRIPT.CONTENT
COLUMNS
LastName VARCHAR2(20) PATH 'LastName'
,course_join XMLType PATH 'AcademicRecord/AademicSession/Course') xt -- next parallel level down
,XMLTable('//AcademicRecord/AademicSession/Course'
PASSING xt.course_join
COLUMNS
course_rno FOR ORDINALITY
,coursetitle_join XMLTYPE PATH 'CourseTitle') xt2
, XMLTable('/CourseTitle'
PASSING xt2.coursetitle_join
COLUMNS
coursetitle_rno for ordinality
,CourseTitle varchar2(40) PATH '.') xt3
;
-- Get no results, no error.
Suggestions?
Thank you in advance for any assistance! I have learned a lot
in my 6 days so far, but lost on this.