Search code examples
xmldatabaseoracle-databaseoracle11goracle-data-integrator

ODI: Load Data from XML file & insert into Oracle Database

I'm newbie at ODI (Oracle Data Integrator) 11g. I have XML file. I need to load data from that XML file into Oracle Database. I'm created project, imported knowledge module, created XML & Oracle model.

Note : My XML File consisted from 40+ table. here's my interface

i'm created XML & Oracle Model

Target datastore is only stores 1 table at moment.

Here's my session log: Error Message

Solution

  • Edit

    Based on the comment, this is a way to load several "same xsd" xml files.

    First, you must ensure that all XML do have the same XSD structure, or else you might experience strange behaviour.

    To process more than one XML file, basically this is what you have to do:

    1) You give a specific fixed XML name, and create your topology based on it. 2) Create a "file processing control" to rename and move the files before reading. 3) Make sure you do the proper "synchronizing" commands

    Example

    You have XML files:

    /path/in/XML001.XML
/path/in/XML002.XML
/path/in/XML003.XML

    Save one of the files as XML_DATA.xml, and setup your topology with XML_DATA.xml in some '/work/' path, verify that it's all good by testing.

    Process a Loop where:

    1) Move /path/in/XML001.XML to /path/work/XMLDATA.XML (overwrite or delete old XMLDATA)
    2) Execute "SYNCHRONIZE FROM FILE"
    3) Process your Interfaces
    4) Execute "SYNCHRONIZE FROM DB"
    5) Move processed XML to "/path/processed/"

    Your Package would be something like:

    [Loop] > [MoveFile] > [ProcedureSync] > [Interfaces] > [ProcedureSync] > [MoveFile] > [EndLoop]

    About the Loop control, there is a few ways to do it, if you have doubts I can send you tips.

    Hope that helps!

    Edit 2

    Based in the new information I will try to send a better explanation about the questions asked. This is not such a difficult mission, but to someone who is still understanding ODI tool it may sound hard.

    The main point is to understand that for ODI a XML file is a data source just like a Database, and not a file like a .csv.

    • (Q) How do i create File Processing Control?
    • (A) What I called "file processing control" is a simple mechanism where you "move/copy/delete" your XML files through the folders. You can accomplish that by using the Package tools OdiFileCopy, OdiFileDelete, etc.
    • (Q) I'm not understand enough about synchronizing command. Can you give me more details?
    • (A) The synchronizing is necessary when using XML files in ODI. Basically when ODI uses a XML file, first it loads it to memory. Then ODI creates a .lck file that locks the XML file. When you finish your Package the XML file is still in memory, so you need to "download" it again to the file and release the lock, and ODI does not do that by it self. This is because you should be able to run as many packages as you see fit in your loading process, with the XML file still available. So when you're finished you must inform that you will not use the XML anymore, by running the synchronizing from database command.
    • Should i create XML Technology topology or File Technology topology? or Both?
    • (A) You don't need to create topology for File, to run the package file tools. Just keep in mind that you must create your XML topology for a "Generic" file. Instead of setting your topology to OM135SVOD180624.xml, you should set it to OMDATASOURCE.xml
    • I need more details on Loop Control
    • (A) I will make a more detailed description. Very unfortunately I dont have an ODI installed here right now, or else I would also post an example. But I think it will be easy to understand.

    1) Example of using Synchronizing when working with XML files.

    Whenever accessing a XML file for reading or writing, it's recomended to proceed as folow:

    • Run "SYNCHRONIZE FROM FILE" command, in the Logical Schema of the XML file. You can do this by creating an ODI Procedure, setting the technology to XML, pointing the Logical Schema to the one you created, and writing in the "command on target" window: SYNCHRONIZE FROM FILE.
    • Run your ODI packages or load plans that read or write the XML file.
    • Run "SYNCHRONIZE FROM DATABASE" command, in the Logical Schema of the XML file. You can do this by creating an ODI Procedure, setting the technology to XML, pointing the Logical Schema to the one you created, and writing in the "command on target" window: SYNCHRONIZE FROM DATABASE.
    • The full reference for this is very easy to find here: https://docs.oracle.com/cd/E28280_01/integrate.1111/e12644/xml_file.htm#ODIKM534 in the item 5.6.1.2.

    Example: enter image description here

    2) File Processing Control

    In the Package, inside the Toolbox bar, there is a "File" toolbar that gives you a lot of useful File tools, like copy, delete, move, zip, unzip, etc. This is useful when you need to "control" the files you are reading, renaming, etc.

    You can play a little with these tools, they are very easy to understand.

    3) Loop Control

    In ODI you can develop a loop using variables in a package. Sometimes you can process loops just by using the odi Procedure, it depends on your needs. Based in the few information I have about your context, I would suggest you try using variables first.

    So here is an example that process a loop 5 times.

    1) Create a Variable as Number.
    2) Drag it to the package and set the value to 0.
    3) Drag it again, configure it as Evaluation, set the condition to Equal to 5.
    4) Drag any "test" interface that you have.
    5) Link the KO Link to the Interface. When using evaluating variables, KO links acts like the "false" condition. In our case, it will point to the interface if the counter is < 5.
    6) Drag the variable again, this time instead of setting a fixed value, you will set an incremental by 1. This will add 1 to the value.
    7) Link this last variable to the Evaluate variable.

    So, this will run 5 times your test interface. I found an image in the internet that ilustrates this:

    Example of Loop in ODI

    You can find useful information here "https://dzone.com/articles/odi-11g-implementing-loops" and "https://blogs.oracle.com/dataintegration/using-variables-in-odi:-creating-a-loop-in-a-package".

    Final Package

    In the end of the day, your "algorithm" would be something like this:

    1) Process the Loop through the Files in the folder (this is a little tricky, it may need a jython code, you can find a reference to do that here "https://blogs.perficient.com/2014/08/01/looping-through-files-in-a-folder-using-odi/")

    2) Move the first file to /work/OMDATASOURCE.xml

    3) SYNCHRONIZE FROM DATABASE command.

    4) Process your Interfaces

    5) SYNCHRONIZE FROM FILE command.

    6) Move OMDATASOURCE.xml to '/processed' or any other control you create.

    7) Process Next File. You may also want to control some stuff using tables, like files readed, files processed, etc.

    8) End Loop (using the loop control examples I sent).

    ODI is very flexible and extensible, you can do everything, in a lot of ways.

    Considerations

    • There are other ways to do this. You can set a variable in the name of the XML file. This would prevent the need for "move and rename", but you will still need to process the loop over the files in the path to get the parameters for the files you need, or at least to have them in a table so you can process the loop and change the values.

    Hope this will help, []'s

    Cheers