This discussion is archived
4 Replies Latest reply: Dec 14, 2012 5:35 AM by 974276 RSS

loading xml into oracle table

974276 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    HI Odie,

    Can you please help?
  • 3. Re: loading xml into oracle table
    odie_63 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks Guru.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points