4 Replies Latest reply: Jul 30, 2013 1:24 PM by orton607 RSS

    load xml file into a database table using browse item

    orton607

      Hello,

       

      Anyone please help me with a possible approach. I have an APEX user interface for the users where in users can upload an xml file into apex table wwv_flow_files using the browse item with a submit button. Now, after loading the xml file into the table wwv_flow_files. I need to read and load the xml data into a table sample_tbl. I am using Oracle APEX 4.0 version and Oracle 10g R2 database. Below is the sample XML file to load. Please help me possible suggestions.

       

      <?xml version="1.0" encoding="UTF-8"?>

      <dataroot generated = .............................>

      <MySample>

      <KeyId>1234</KeyId>

      <KeyName>eddskc</KeyName>

      <ScheduleDate>2013-06-16T00:00:00</ScheduleDate>

      <StartTime>2013-06-16T08:00:00</StartTime>

      <EndTime>2013-06-16T08:30:00</EndTime>

      </MySample>

      <MySample>

      <KeyId>5678</KeyId>

      <KeyName>jppdf</KeyName>

      <ScheduleDate>2013-05-19T00:00:00</ScheduleDate>

      <StartTime>2013-05-19T08:00:00</StartTime>

      <EndTime>2013-05-19T08:30:00</EndTime>

      </MySample>

      </dataroot>

       

      Thanks in advance.

       

      Thanks,

      Orton

        • 1. Re: load xml file into a database table using browse item
          Mike Kutz

          I'm on 11g.  I don't know if what I am using is available in 10g or not

           

          Also, you'll need to work out the INSERT..SELECT part in addition to the XMLTable() parameters to match.

          The oracle documentation on the XML function XMLTable() will be your best resource.

           

          I store my code in packages.  This is pretty much a cut+paste of the code I use:

           

            procedure parse_apex_XML( p_filename in varchar2, p_username in varchar2)
            as
              l_xml  XMLType;
            begin
              /* get file from APEX WWV_FLOW_FILES */
              begin
                  select XMLType( blob_content
                        -- more values: http://www.mydul.net/charsets.html
                        ,871 -- which character set? 871==UTF-8
                      )
                    into l_XML
                  from wwv_flow_files where name = p_filename;
              exception
                when no_data_found then
                  raise_application_error( -20001, 'not in FLOW: ''' || p_filename || '''' );
              end;
          
             insert ...
             select ...
             from XMLTable( '/'
                      passing l_xml
                      columns
                      ...
            );
          end;
          
          • 2. Re: load xml file into a database table using browse item
            orton607

            Hello Mike,

             

            Thanks for the approach. However I have one question when retrieving the datetime from the xml file. I have xml tag <StartTime>2013-08-14T15:30:00</StartTime> so, how do i convert this to oracle date format. Please help.

             

            I want to store the value in the database; format to be something like this  8/14/2013 3:30:00 PM.

             

            My sample_tbl has a field start_time and its datatype is DATE.

             

            Thanks,

            Orton

            • 3. Re: load xml file into a database table using browse item
              Mike Kutz

              orton607 wrote:

               

              My sample_tbl has a field start_time and its datatype is DATE.

               

              Good.  Just remember to always store dates as DATE.

              The application (in this case, APEX) should format the date date type into the appropriate human readable format.

               

              The problem with the incoming data is the 'T'.  You'll need to convert that to a space with the REPLACE() command as part as your SELECT statement

              From there, conversion to the DATE data type is simply

              to_date( replace( start_time_str, 'T', ' '), 'YYYY-MM-DD HH24:MI:SS') as start_time
              

               

              And, just in case, the conversion from DATE to your desired format is:

              to_char( start_time, 'MM/DD/YYYY HH12:MI:SS AM')
              

               

              Although, I am getting the 'hours' as being left-padded with zeros... I'd have to look up how/if you can get rid of the zero(0) prefix.

              • 4. Re: load xml file into a database table using browse item
                orton607

                Thanks Mike for all your suggestions.

                 

                Thanks,

                Orton