2 Replies Latest reply: Jan 16, 2013 12:38 PM by 982082 RSS

    How to select XML value for a namespace when multiple namespaces

    982082
      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
      ;
                  
        • 1. Re: How to select XML value for a namespace when multiple namespaces
          Jason_(A_Non)
          Well, based on the SQL you provided, you simply need to correct the XPATH in the NCESCode line so that it reads
                               ,NCESCode  varchar2(20) path 'UserDefinedExtensions/ns2b:TranscriptExtensions/NCESCode'
          I'm assuming there is more to your XML than you showed, since
          StudentLevel/StudentLevelCode
          is not in the XML, but is in your query.
          • 2. Re: How to select XML value for a namespace when multiple namespaces
            982082
            <<I'm assuming there is more to your XML than you showed, since
            StudentLevel/StudentLevelCode
            is not in the XML, but is in your query. >>

            Yes, to simplify, I left out some of the additional XML data, which is typically present, sorry for any confusion. I should have removed those references to that data in my example which was failing to retrieve the NCESCode data which was denoted by that namespace.

            Thank you very much! Your correction worked. I was not understanding until your correction how to properly reference in the XPATH for that namespace value. I'm a newbie at this, and this is my second post. But I've been able to populate quite a few relational tables and that was the first of several namespace tags I will have to deal with next, and with that help, I should be good with that syntax now.

            Thanks again for your help on this.