This discussion is archived
3 Replies Latest reply: Jan 10, 2013 8:16 AM by Jason_(A_Non) RSS

How to Select Parent/Child Related Data from XMLTYPE table

982082 Newbie
Currently Being Moderated
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.
  • 1. Re: How to Select Parent/Child Related Data from XMLTYPE table
    odie_63 Guru
    Currently Being Moderated
    Welcome to the forum!

    Thanks for this well-detailed post.
    Don't forget to use &#x7b;code} tags to preserve formatting of code snippets.

    Your last attempt is the closest but you're not passing the correct set of nodes to the next XMLTable (xt to xt2).

    You can do it like this :
    SQL> select x1.LastName
      2       , x2.GradeLevel
      3       , x3.CourseTitle
      4  from transcript t
      5     , xmltable(
      6         xmlnamespaces(
      7           'urn:org:pesc:message:AcademicRecordBatch:v1.0.0' as "ns0"
      8         , 'urn:org:pesc:message:HighSchoolTranscript:v1.0.0' as "ns1"
      9         )
     10       , '/ns0:AcademicRecordBatch/ns1:HighSchoolTranscript/Student'
     11         passing t.content
     12         columns LastName       varchar2(20) path 'Person/Name/LastName'
     13               , AcademicRecord xmltype      path 'AcademicRecord'
     14       ) x1
     15     , xmltable(
     16         '/AcademicRecord/AcademicSession'
     17         passing x1.AcademicRecord
     18         columns GradeLevel varchar2(20) path 'StudentLevel/StudentLevelCode'
     19               , Courses      xmltype      path 'Course'
     20       ) x2
     21     , xmltable(
     22         '/Course'
     23         passing x2.Courses
     24         columns CourseTitle varchar2(40) path 'CourseTitle'
     25       ) x3
     26  where x1.LastName = 'Smith' ;
     
    LASTNAME             GRADELEVEL           COURSETITLE
    -------------------- -------------------- ----------------------------------------
    Smith                NinthGrade           PHYS ED 101
    Smith                TenthGrade           CALCULUS 201
    Smith                TenthGrade           ZOOLOGY 202
     
    When you need to extract a parent-child relationship, always pass the set of child nodes (as XMLType) to another XMLTable so that they can be processed in their turn.
    In the present case : Student, then AcademicSession, then Course.
    It's also possible to use a single XMLTable (and a more complex XQuery) to resolve the hierarchy, I'll show you if you're interested.

    Also, whenever possible, always use full XPath expressions, starting from the root node.
  • 2. Re: How to Select Parent/Child Related Data from XMLTYPE table
    982082 Newbie
    Currently Being Moderated
    Thank you for help. It is very much appreciated. I'll need to study how you reference each parent/child node carefully in your provided solution, which I have already tested successfully, and use that technique in a few more simple queries. Once I have that under my belt, I will follow-up with your offer of a more complex query to use a single XMLTable (and a more complex XQuery) to resolve the hierarchy. I'll make it a practice to use full XPath expressions, starting from the root node, as you recommended. One thing I will try is a query that allows me to handle one large XML file from this educational vendor which includes multiple student transcripts within that one file. With the suggestions you have provided so far, I believe I will be able to determine how to do that, as well. Terrific forum. For my next post, I will format my posted XML information using code tags for indentation/appearance. Thank you!
  • 3. Re: How to Select Parent/Child Related Data from XMLTYPE table
    Jason_(A_Non) Expert
    Currently Being Moderated
    Just wanted to add in a comment since you are on 11.2.0.2. In that version, Oracle made SECUREFILE BINARY the default storage option for XMLType columns so
    create table TRANSCRIPT (
    CONTENT xmltype
    )
    xmltype column CONTENT store as securefile binary xml;
    is equivalent to
    create table TRANSCRIPT (
    CONTENT xmltype
    );

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points