Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Load XML file Into Oracle External Table

Reva812Jun 4 2015 — edited Jul 21 2015


I have to load XML file data into a Oracle staging table using External Tables.

Let's say below is my XML file

<Header>
<A_CNT> 10 </A_CNT>
<E_CNT> 10 </E_CNT>
<AF_CNT> 10 </AF_CNT>
</Header>
<Students>
  <Students-Details>
    <Student_info>
      <Single_Info>
        <ID>18</ID>
        <City>NewYork</City>
        <Country>US</Country>
        <Name_lst>
          <Student_name>
            <First_name>Samuel</First_name>        
            <Last_name>Paul</Last_name>
            <DOB>19871208</DOB>
            <RecordStatus>Current</RecordStatus>
          </Student_name>
          <Student_name>
            <First_name>Samuel</First_name>        
            <Last_name>Paul</Last_name>
            <DOB>19871208</DOB>

            <TerminationDt>20050812</TerminationDt>
            <RecordStatus>History</RecordStatus>
          </Student_name>
        </Name_lst>
        <Personal_Info>
          <Gender>Male</Gender>
          <Age>27</Age>
        </Personal_Info>   
      </Single_Info> 
     </Student_info>

     <Student-Enrol>
      <Class-A>
       <Info>
        <Detail>
          <Student-ID>18</Student-ID>
          <Major> EE </Major>
          <Course-Grades>
             <Course>VLSI</course>
             <Grade>3.0</Grade>
          </Course-Grades>
          <Course-Grades>
             <Course>NanoTechnology</course>
             <Grade>4.0</Grade>
          </Course-Grades>
        </Detail>
        <Detail>
          <Student-ID>18</Student-ID>
          <Major> CE </Major>
        </Detail>
       </Info>
      <Class-A>
    <Student_Enrol>
  <Students-Details>
<Students>

I have to load this XML file data into one single table using an External Table. Could anyone please help me with coding.

Thanks,

Reva

This post has been answered by odie_63 on Jun 16 2015
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 18 2015
Added on Jun 4 2015
49 comments
16,207 views