Database Software

Hi,

I'm a beginner with this, but I'm doing well with your help from this forum in a recent post selecting out all the detail from my xml
out into my oracle relational tables. Stumped, though, on how to select a value for xml tag value referenced by a defined namespace.

Version, XML, what I want to select, and attempted sql is below. Thanks in advance!

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

<?xml version="1.0" encoding="UTF-8"?>
<arb:AcademicRecordBatch xmlns:arb="urn:org:pesc:message:AcademicRecordBatch:v1.0.0">
  <hst:HighSchoolTranscript xmlns:hst="urn:org:pesc:message:HighSchoolTranscript:v1.0.0" xmlns:ct="http://ct.transcriptcenter.com">
   <TransmissionData>
      <DocumentID>2013-01-02T09:06:15|D123456789</DocumentID>
   </TransmissionData>
   <Student>
            <Person>
                <Name>
                    <FirstName>John</FirstName>
                    <LastName>Doe</LastName>                    
                </Name>
            </Person>
            <AcademicRecord>  
                <AcademicSession>
                    <Course>
                        <CourseTitle>KEYBOARD 101</CourseTitle>
                        <UserDefinedExtensions>
                          <ct:TranscriptExtensions>
                             <NCESCode>01001E010456</NCESCode>
                             <CourseRigor>1</CourseRigor>
                          </ct:TranscriptExtensions>
                      </UserDefinedExtensions>
                    </Course>
                    <Course>
                        <CourseTitle>SCIENCE 101</CourseTitle>
                        <UserDefinedExtensions>
                          <ct:TranscriptExtensions>
                             <NCESCode>01001E010457</NCESCode>
                             <CourseRigor>2</CourseRigor>
                          </ct:TranscriptExtensions>
                      </UserDefinedExtensions>                        
                    </Course>
                </AcademicSession>
                <AcademicSession>
                    <Course>
                        <CourseTitle>MATH 201</CourseTitle>
                        <UserDefinedExtensions>
                          <ct:TranscriptExtensions>
                             <NCESCode>01001E010458</NCESCode>
                             <CourseRigor>2</CourseRigor>
                          </ct:TranscriptExtensions>
                      </UserDefinedExtensions>                                  
                    </Course>
                </AcademicSession>
         </AcademicRecord>
   </Student>
  </hst:HighSchoolTranscript>
</arb:AcademicRecordBatch>

I want to be able to select the NESCODE associated to each coursetitle (01001E010456, 01001E010457, 01001E010458), with NESCode defined by namespace, but getting out NULL.

DOCUMENTID LASTNAME COURSETITLE NCESCODE
2013-01-02T09:06:15|D123456789 Doe KEYBOARD 101
2013-01-02T09:06:15|D123456789 Doe SCIENCE 101
2013-01-02T09:06:15|D123456789 Doe MATH 201

My SQL is below. You'll see where I commented out a couple failed alternatives too. Thanks again in advance for any guidance.

   select x0.DocumentID
         ,x1.LastName
         , x3.CourseTitle
         ,x3.NCESCode
  from TRANSCRIPT t
     , xmltable(                                                                                    
         xmlnamespaces(
           'urn:org:pesc:message:AcademicRecordBatch:v1.0.0' as "ns0" 
         , 'urn:org:pesc:message:HighSchoolTranscript:v1.0.0' as "ns1"
        --, 'http://ct.transcriptcenter.com'                               as "ns1b"  
         )
      , '/ns0:AcademicRecordBatch/ns1:HighSchoolTranscript'  
        passing t.content
        columns DocumentID       varchar2(40) path 'TransmissionData/DocumentID' 
                   , Student xmltype      path 'Student'      
      ) x0 
   , xmltable(
        '/Student' 
        passing x0.Student 
        columns LastName varchar2(20) path 'Person/Name/LastName'                        
                    ,AcademicRecord   xmltype      path 'AcademicRecord'  
      ) x1           
   , xmltable(
        '/AcademicRecord/AcademicSession'  
        passing x1.AcademicRecord
        columns GradeLevel varchar2(20) path 'StudentLevel/StudentLevelCode'
              , Courses      xmltype      path 'Course'
      ) x2
          , xmltable(
          xmlnamespaces('http://ct.transcriptcenter.com'  as "ns2b")
          , '/Course'
        passing x2.Courses
        columns CourseTitle varchar2(40) path 'CourseTitle'
                     ,NCESCode  varchar2(20) path 'UserDefinedExtensions/ns2b:ct/NCESCode'
                     --,NCESCode  varchar2(20) path 'UserDefinedExtensions/ns2b:ct/TranscriptExtensions/NCESCode'                      
      ) x3
;