5 Replies Latest reply: Oct 6, 2013 3:39 PM by Saxena RSS

    Load XML data from UNIX Server Directly into Relational Database Tables

    Saxena

      Is there a way I can load data from an XML File into Oracle Tables , without having the Input XML file in some Oracle Server Directory. My XML File resides on UNIX Application server. And I need to directly load the data into Database tables. Without loading them into the Database Directory.


      Also I am looking for a solution that would not load my Database much and effect other running processes. Can it be done using SQL Loader ?


      Oracle Database Version is : Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

        • 1. Re: Load XML data from UNIX Server Directly into Relational Database Tables
          odie_63

          Yes, SQL*Loader can load the file into an XMLType table or column, but it won't parse it for you.

          You'll have to implement a second step to parse the input XML document and perform the necessary DML operations on your relational tables.

           

          To sum up, I would :

          1. create a staging XMLType table
          2. Load the file into that table using SQL*Loader
          3. Parse the XML using XQuery (XMLTable, XMLQuery, XMLExists functions)

           

          You'll find tons of examples of the last step in this forum.

          • 2. Re: Load XML data from UNIX Server Directly into Relational Database Tables
            Saxena

            Thanks for your reply ,

             

            • Please would you quote an Example about : 'Load the file into that table using SQL*Loader'  (From UNIX Server) Or instance of some existing thread that relates to my situation.

             

            • The Size of the File would be about 3 GB. For a similar requirement one of my peers Code which used XMLTABLE and XPATH Approach consumed a lot of resources while running and caused the other Database Applications to slow down. Thus those guys have come up with an approach to :

             

                    Parse XML using a C Code using some STRING Functions =>  For a CSV or Fixed width .dat file and then use SQL Loader to just load the file into Tables.

             

                    This approach is efficient in terms of Resources and Time(Takes 5 mins). But I am not confident about parsing XML based on String based C Functions.

                     Please comment about this approach . Also if possible Suggest the best efficient way of doing this.

            • 3. Re: Load XML data from UNIX Server Directly into Relational Database Tables
              odie_63

              The Size of the File would be about 3 GB. For a similar requirement one of my peers Code which used XMLTABLE and XPATH Approach consumed a lot of resources while running and caused the other Database Applications to slow down.

               

              That's a different story then.

              Did they try with a temporary Binary XML storage ?

               

              Schema-based structured storage is probably the best option in this case.

              See the FAQ for some pointers : XML DB FAQ

               

              We'll further advise if you think you can use this approach.

              • 4. Re: Load XML data from UNIX Server Directly into Relational Database Tables
                Marco Gralike

                Have look at Client Side Encoding, that is encode on the client the document already in Binary XML (Using Binary XML for C) .


                There is no need for "Parse XML using a C Code using some STRING Functions". Oracle provides the XDK (XML Development Kit) package for handling in Java, C or C++

                • 5. Re: Load XML data from UNIX Server Directly into Relational Database Tables
                  Saxena

                  Thanks for the replies mates !!!

                   

                  It did help me learn lot of options.

                   

                  But the simplest using UNIX server that worked out for me was to use an XSLT to convert the XML file into a delimited file.(Load the unix server, save Oracle resources).

                  UNIX functionality 'xsltproc' was not a bad option.

                   

                  Then use SQL Loader to load into tables.

                   

                  Cheers !!

                  Rahul