3 Replies Latest reply: Jan 10, 2013 10:16 AM by Jason_(A_Non) RSS

    How to Select Parent/Child Related Data from XMLTYPE table

    982082
      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
          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
            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)
              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
              );