1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Design document to read and parse xml to oracle

Discussion in 'SQL PL/SQL' started by kristenssk, Oct 4, 2018.

  1. kristenssk

    kristenssk Newly Initiated

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    India
    Experts,

    I am new to Oracle and XML world.

    Please find below my requirement.

    I don't have any Directory created and have no SQL Loader/SQL Plus. My requirement is to read XML file and Parse it into Oracle Tables ( Load the data ( required columns data to Oracle tables).

    1. Can any please provide any Technical design document to complete this process.

    2. Do we have any package/procedure to do this process.

    Have gone through Google found many. But I didn't get any clear c=idea.

    Suggestions would be appreciated.

    Regards
    Kris
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    767
    Likes Received:
    147
    Trophy Points:
    830
    Location:
    Russian Federation
    How complex a document you plan to create : with XSD scheme or not ?
     
  3. kristenssk

    kristenssk Newly Initiated

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    India
    We are getting all the files in a .ZIP format then am extracting these ZIP files manually to a Specific Folder. These folder contains many subfolders.

    Is there any way to extact the zip file instead of doing manually every time. Can you please provide the sctipt if you have any.

    1, Each folder contains many .xml files.

    2. Have created a table having only one columns which is of XMLTYPE.

    Create table xml_load
    (
    data_load XMLTYPE
    );


    3. Then am loading each XML file into "xml_load" table.

    4. After insertion,

    following the below syntax to parse the xml file to Oracle Tables. Not sure How is it working from backend.

    Code (SQL):
    INSERT INTO EMP(Ename,dname,job)
           SELECT  x.*  
    FROM spim.xml_in tbl
       , xmltable(
              '//Image'
              passing tbl.xml_doc
              COLUMNS
               Ename    varchar2(40)  path '@path'   ---> Based on XML tags we are defining PATH
               ,dname   varchar2(20)  path '@path'   ---> Based on XML tags we are defining PATH
               ,dname   varchar2(20)  path '@path'   ---> Based on XML tags we are defining PATH
         ) x;  
    Apart from these steps , I am not sure How it is reading the XML file and parse the data into Oracle Table.

    It would be great if you can provide me any document to better understand in pictorial view.

    Thank you.
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    767
    Likes Received:
    147
    Trophy Points:
    830
    Location:
    Russian Federation

    Are you need stored source of xml documents?
    Task could be solved of a few path: by shell scripts and (sqlplus or sql loader), simple of java class and saving into the your flat table.

    Therefore,a one of questions :which is option to you need?
     
  5. kristenssk

    kristenssk Newly Initiated

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    India
     
  6. kristenssk

    kristenssk Newly Initiated

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    India
    It would be if you can provide me a Stored Procedure.

    Thanks,
    Kris