4 Replies Latest reply: Dec 14, 2012 7:35 AM by 974276 RSS

    loading xml into oracle table

    974276
      hi all,
      i am new to xml .can you please help me with basic requirements for loading xml files or suggestion.

      1. source xml files are not in fixed format. can we handle this with xmltable?
      if yes please provide links so that i can refer.

      2.is it possible to load data into oracle table if element tag is missing .if,yes how to handle?
      ex: <test> quarterly </test> in some files i am getting this details and other files i am not getting.

      2..is it possible to load data into oracle table if node is missing .if,yes how to handle?

      <result>
      <results>
      <english> 90 </english>
      <hindi> 80</hindi>
      </results>
      <results>
      <english> 70 </english>
      <hindi> 50</hindi>
      </results>

      It is very urgent. please help
        • 1. Re: loading xml into oracle table
          974276
          Hi Gurus,

          can you please reply??

          http://odieweblog.wordpress.com/2012/05/10/how-to-load-xml-data-into-multiple-tables/

          I have gone through the above link: but for missing node/tag i didn't find solution.

          for example if the <Departments/> is missing in xml file then how to handle the situation.

          below example:

          </Location>
          <Location id="3100">
          <address>Pieter Breughelstraat 837</address>
          <postalCode>3029SK</postalCode>
          <city>Utrecht</city>
          <stateProvince>Utrecht</stateProvince>
          <countryId>NL</countryId>
          <Departments/> --here it will load null values we are getting tag is closed
          </Location>
          <Location id="3200">
          <address>Mariano Escobedo 9991</address>
          <postalCode>11932</postalCode>
          <city>Mexico City</city>
          <stateProvince>Distrito Federal,</stateProvince>
          <countryId>MX</countryId>
          <Location> --here the department tag is missing

          Please help
          • 2. Re: loading xml into oracle table
            974276
            HI Odie,

            Can you please help?
            • 3. Re: loading xml into oracle table
              odie_63
              for example if the <Departments/> is missing in xml file then how to handle the situation.
              Did you try it?
              There's nothing specific to do, it will work the same regardless the element is empty or missing.

              For example, if you have :
              <Locations>
                <Location id="1000">
                  <address>1297 Via Cola di Rie</address>
                  <postalCode>00989</postalCode>
                  <city>Roma</city>
                  <countryId>IT</countryId>
                </Location>
                <Location id="1100">
                  <address>93091 Calle della Testa</address>
                  <postalCode>10934</postalCode>
                  <city>Venice</city>
                  <countryId>IT</countryId>
                  <Departments/>
                </Location>
                <Location id="2700">
                  <address>Schwanthalerstr. 7031</address>
                  <postalCode>80925</postalCode>
                  <city>Munich</city>
                  <stateProvince>Bavaria</stateProvince>
                  <countryId>DE</countryId>
                  <Departments>
                    <Department id="70">
                      <name>Public Relations</name>
                      <managerId>204</managerId>
                      <Employees>
                        <Employee id="204">
                          <firstName>Hermann</firstName>
                          <lastName>Baer</lastName>
                          <email>HBAER</email>
                          <phoneNo>515.123.8888</phoneNo>
                          <hireDate>2002-06-07</hireDate>
                          <jobId>PR_REP</jobId>
                          <salary>10000</salary>
                          <managerId>101</managerId>
                        </Employee>
                      </Employees>
                    </Department>
                  </Departments>
                </Location>
              </Locations>
              For Location 1000, there's no <Departments>
              For Location 1100, there's an empty <Departments> element
              For Location 2700, there's one Department with one Employee.

              Then :
              SQL> SELECT l.location_id
                2       , l.street_address
                3       , l.postal_code
                4       , l.city
                5       , l.state_province
                6       , l.country_id
                7       , d.department_id
                8       , d.department_name
                9       , d.dept_manager_id
               10       , d.department_rno
               11       , e.employee_id
               12       , e.first_name
               13       , e.last_name
               14       , e.email
               15       , e.phone_number
               16       , e.hire_date
               17       , e.job_id
               18       , e.salary
               19       , e.commission_pct
               20       , e.manager_id
               21       , e.employee_rno
               22  FROM tmp_xml t
               23     , XMLTable('/Locations/Location'
               24         passing t.object_value
               25         columns
               26           location_rno   for ordinality
               27         , location_id    number(4)    path '@id'
               28         , street_address varchar2(40) path 'address'
               29         , postal_code    varchar2(12) path 'postalCode'
               30         , city           varchar2(30) path 'city'
               31         , state_province varchar2(25) path 'stateProvince'
               32         , country_id     varchar2(2)  path 'countryId'
               33         , departments    xmltype      path 'Departments'
               34       ) l
               35     , XMLTable('/Departments/Department'
               36         passing l.departments
               37         columns
               38           department_rno  for ordinality
               39         , department_id   number(4)    path '@id'
               40         , department_name varchar2(30) path 'name'
               41         , dept_manager_id number(6)    path 'managerId'
               42         , employees       xmltype      path 'Employees'
               43       ) (+) d
               44     , XMLTable('/Employees/Employee'
               45         passing d.employees
               46         columns
               47           employee_rno   for ordinality
               48         , employee_id    number(6)    path '@id'
               49         , first_name     varchar2(20) path 'firstName'
               50         , last_name      varchar2(25) path 'lastName'
               51         , email          varchar2(25) path 'email'
               52         , phone_number   varchar2(20) path 'phoneNo'
               53         , hire_date      date         path 'hireDate'
               54         , job_id         varchar2(10) path 'jobId'
               55         , salary         number(8,2)  path 'salary'
               56         , commission_pct number(2,2)  path 'commPct'
               57         , manager_id     number(6)    path 'managerId'
               58       ) (+) e
               59  ;
               
              LOCATION_ID STREET_ADDRESS                           POSTAL_CODE  CITY                           STATE_PROVINCE            COUNTRY_ID DEPARTMENT_ID DEPARTMENT_NAME                DEPT_MANAGER_ID DEPARTMENT_RNO EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER         HIRE_DATE   JOB_ID         SALARY COMMISSION_PCT MANAGER_ID EMPLOYEE_RNO
              ----------- ---------------------------------------- ------------ ------------------------------ ------------------------- ---------- ------------- ------------------------------ --------------- -------------- ----------- -------------------- ------------------------- ------------------------- -------------------- ----------- ---------- ---------- -------------- ---------- ------------
                     1000 1297 Via Cola di Rie                     00989        Roma                                                     IT                                                                                                                                                                                                                                                           
                     1100 93091 Calle della Testa                  10934        Venice                                                   IT                                                                                                                                                                                                                                                           
                     2700 Schwanthalerstr. 7031                    80925        Munich                         Bavaria                   DE                    70 Public Relations                           204              1         204 Hermann              Baer                      HBAER                     515.123.8888         07/06/2002  PR_REP       10000,00                       101            1
               
              • 4. Re: loading xml into oracle table
                974276
                Thanks Guru.