5 Replies Latest reply: Jul 24, 2013 1:56 PM by OmarYañez RSS

    Insert file xml in table

    OmarYañez

      Hi all

       

      Looking for how to create an application, that allows me to upload an xml file and insert the file into a table, something like what makes apex, with "Data Workshop -> Data Load -> XML Data".

       

       

      I tried to create it using this link https://forums.oracle.com/message/9170494 # 9170494

       

       

      but when loading the file and the submit (running the "sp") sends the error "ORA-22285: non-existent directory or file for FILEOPEN operation".

       

       

      I could auxiliary slightly

       

       

      regards

        • 1. Re: Insert file xml in table
          jrimblas

          Hola Omar,

          The error you got is because you actually need to create a directory object in the database.  This should help CREATE DIRECTORY

          In the example you reference TEST_DIR is the actual Directory Object that was created.

           

          Now, that said, in that example they are reading an XML file and parsing it.  Do you need to store the XML or parse and process the values?

           

          You can probably use the standard APEX functionality for loading CLOB or BLOB. If you want to process them you can do that with a Process that call your own code and split the XML.

           

          If you're in APEX 4.1 or 4.2 then you can create a Data Load wizard that does just what the "Data Workshop -> Data Load -> XML Data" does.  Perhaps this is exactly what you're looking for.  To get started, create a New Page and select "Data Loading".

           

          Thanks

          -Jorge

          http://rimblas.com/blog/

          • 2. Re: Insert file xml in table
            OmarYañez

            Hi jrimblas

             

            Thanks for responding

             

             

            I forgot to mention, the directory already believe in the database, but from what I understood, I also need to create it in Apex.

             

             

            I need to parse the values to be inserted in the table.

             

             

            Use APEX 4.2.2

             

             

            The data load wizard, does what I want, but with csv files. I would like to use that, but xml file, is it possible?

             

             

             

            Regards

            • 3. Re: Insert file xml in table
              Mike Kutz

              This whole idea is perplexing to me.

               

              XML files SHOULD NOT BE CHANGING.  That is there purpose.  You have a DTD/XST that defines where in the XML hierarchy a piece of data is and (therefore) how to access it.  With that information, you can hard-code stuff.

               

              As such, you should have no reason for the "map data column to table column" feature of Data Loader since all the mappings will always be the same.

              As simple INSERT.. SELECT can (and should) be used.

              (use MERGE if you are updating/inserting the data.)

               

              I copy+pasted some of my code from a procedure I use.

              You'll need to replace the parameter name (p_filename) with the appropriate bind value (item name)

               

               

                declare
                  l_XML XMLType;
                begin
                  /* get file from APEX WWV_FLOW_FILES */
                  begin
                      select XMLType( blob_content, 1 ) -- 1 means that the file is USACII encoded. this may need to be changed.
                        into l_XML
                      from wwv_flow_files where name = p_filename; -- replace this with the appropriate ITEM NAME
                  exception
                    when no_data_found then
                      raise_application_error( -20001, 'not in FLOW: ''' || p_filename || '''' );
                  end;
              
                  /* you'll need to figure this one out */
                  INSERT INTO T (...)
                  SELECT ..
                  FROM  XMLTable( '/'
              pasing l_xml
                          columns
                               <column name> <column type> PATH <xpath> -- repeat for each "column" you want to extract
                      );
              
                 delete from wwv_flow_files where name=p_filename;
                end;
              
              • 4. Re: Insert file xml in table
                jrimblas

                I forgot to mention, the directory already believe in the database, but from what I understood, I also need to create it in Apex.

                 

                If the directory object has been created does does it belong to your Parsing Schema in your application?  Or do you have rights on it?

                 

                And yes, you can do it with XML and example you reference with perhaps what Mike mentions will work.

                 

                Thanks

                -Jorge

                • 5. Re: Insert file xml in table
                  OmarYañez

                  jrimblas escribió:

                   

                  I forgot to mention, the directory already believe in the database, but from what I understood, I also need to create it in Apex.

                   

                  If the directory object has been created does does it belong to your Parsing Schema in your application?  Or do you have rights on it?

                   

                  And yes, you can do it with XML and example you reference with perhaps what Mike mentions will work.

                   

                  Thanks

                  -Jorge

                   

                  Thank you both for your responses, I could complete my requirement utlizando the code I provide mike