6 Replies Latest reply: Aug 10, 2012 10:43 AM by 954719 RSS

    Importing XML into oracle 11g database

    954719
      I am having some difficulty parsing an XML file into oracle 11g database.

      Currently using Oracle 11g Express Edition (XE)

      Here is how my XML file looks like:


      <?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
      - <AccountMap xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      - <Accounts>
      - <AccountMapping>
      <AccountID>0000000000000-000</AccountID>
      <AccountName>XLS</AccountName>
      <AccountType>Excel</AccountType>
      <AccountOwner>System</AccountOwner>
      </AccountMapping>
      - <AccountMapping>
      <AccountID>0000000000000-001</AccountID>
      <AccountName>XLSS</AccountName>
      <AccountType>Excel2</AccountType>
      <AccountOwner>System2</AccountOwner>
      </AccountMapping>
      - <AccountMapping>

      and so on the file continues.. I have the xml file saved on my local hard disk

      I have created a package in SQL Developer

      create or replace
      PACKAGE XML_FILEHANDLER AS

      type TRecord is record (
      Account_ID varchar2(100)
      , AccountName varchar2(100)
      , AccountType varchar2(30)
      , AccountOwner varchar2(100)
      );

      type TRecordTable is table of TRecord;

      function getRows (p_directory in varchar2, p_filename in varchar2) return TRecordTable pipelined;

      END XML_FILEHANDLER;

      -- BODY

      create or replace
      PACKAGE BODY XML_FILEHANDLER AS

      function getRows (p_directory in varchar2, p_filename in varchar2) return TRecordTable pipelined AS

      nb_rec NUMBER := 1;
      tmp_xml CLOB;
      tmp_file CLOB;
      rec TRecord;

      BEGIN

      dbms_lob.createtemporary(tmp_file, true);
      tmp_file := dbms_xslprocessor.read2clob(p_directory, p_filename);

      rec.Account_ID := regexp_replace(tmp_file, '.*<Account_ID>(.*)</Account_ID>.*', '\1', 1, 1, 'n');
      rec.AccountName := regexp_replace(tmp_file, '.*<AccountName>(.*)</AccountName>.*', '\1', 1, 1, 'n');
      rec.AccountType := regexp_replace(tmp_file, '.*<AccountType >(.*)</AccountType >.*', '\1', 1, 1, 'n');
      rec.AccountOwner := regexp_replace(tmp_file, '.*<AccountOwner>(.*)</AccountOwner>.*', '\1', 1, 1, 'n');

      loop

      -- this regexp finds occurrence(s) of this pattern : "<?xml ... ?><root_tag> ... </root_tag>"
      tmp_xml := regexp_substr(tmp_file, '<\?xml[^?]+\?>\s+<([^>]+)>.*?</\1>', 1, nb_rec, 'n');
      exit when length(tmp_xml) = 0;
      --dbms_output.put_line(tmp_rec);
      nb_rec := nb_rec + 1;

      SELECT Account_ID, AccountName, AccountType, AccountOwner
      into rec.Account_ID, rec.AccountName, rec.AccountType, rec.AccountOwner
      from xmltable(
      'Accounts/AccountMapping' passing xmltype(tmp_xml) columns
      Account_ID varchar2(100) path 'Account_ID'
      , AccountName varchar2(100) path 'AccountName'
      , AccountType varchar2(30) path 'AccountType'
      , AccountOwner varchar2(100) path 'AccountOwner'
      );

      pipe row ( rec );

      end loop;

      dbms_lob.freetemporary(tmp_file);

      END getRows;

      END XML_FILEHANDLER;

      -- I am calling my function using the following sql query

      select * from table(XML_FileHandler.getRows('XML', 'test.xml'));

      Here is the error I am receiving

      ORA-29283: invalid file operation
      ORA-06512: at "SYS.UTL_FILE", line 536
      ORA-29283: invalid file operation
      ORA-06512: at "XDB.DBMS_XSLPROCESSOR", line 265
      ORA-06512: at "user.XML_FILEHANDLER", line 13
      29283. 00000 - "invalid file operation"
      *Cause:    An attempt was made to read from a file or directory that does
      not exist, or file or directory access was denied by the
      operating system.
      *Action:   Verify file and directory access privileges on the file system,
      and if reading, verify that the file exists.

      I have done the following

      - Provided appropriate access to user (read, write) including directory access rights
      - ensured that directory exists
      - ensured that file exists

      I have searched all over google and metalink but am unable to get this to run.. please help!
        • 1. Re: Importing XML into oracle 11g database
          954719
          Ok so now I am able to access the dir and the file and dont receive that error anymore - it had something to do with the file security, so I created a new file and copied the content.

          The issue now is that the query keeps on running forever and I can't seem to get any results.. I believe something is wrong with the parsing query..

          any help??
          • 2. Re: Importing XML into oracle 11g database
            AlexAnd
            >
            SELECT Account_ID, AccountName, AccountType, AccountOwner
            into rec.Account_ID, rec.AccountName, rec.AccountType, rec.AccountOwner
            from xmltable(
            'Accounts/AccountMapping' passing xmltype(tmp_xml) columns
            Account_ID varchar2(100) path 'Account_ID'
            , AccountName varchar2(100) path 'AccountName'
            , AccountType varchar2(30) path 'AccountType'
            , AccountOwner varchar2(100) path 'AccountOwner'
            );
            >
            change to
            SELECT Account_ID, AccountName, AccountType, AccountOwner
            into rec.Account_ID, rec.AccountName, rec.AccountType, rec.AccountOwner
            from xmltable(
            'AccountMap/Accounts/AccountMapping' passing xmltype(tmp_xml) columns
            Account_ID varchar2(100) path 'Account_ID'
            , AccountName varchar2(100) path 'AccountName'
            , AccountType varchar2(30) path 'AccountType'
            , AccountOwner varchar2(100) path 'AccountOwner'
            );
            SQL> SELECT Account_ID, AccountName, AccountType, AccountOwner
              2  --into rec.Account_ID, rec.AccountName, rec.AccountType, rec.AccountOwner
              3  from xmltable(
              4  'AccountMap/Accounts/AccountMapping' passing
              5  xmltype('<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
              6  <AccountMap xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
              7  <Accounts>
              8  <AccountMapping>
              9  <AccountID>0000000000000-000</AccountID>
             10  <AccountName>XLS</AccountName>
             11  <AccountType>Excel</AccountType>
             12  <AccountOwner>System</AccountOwner>
             13  </AccountMapping>
             14  <AccountMapping>
             15  <AccountID>0000000000000-001</AccountID>
             16  <AccountName>XLSS</AccountName>
             17  <AccountType>Excel2</AccountType>
             18  <AccountOwner>System2</AccountOwner>
             19  </AccountMapping>
             20  </Accounts>
             21  </AccountMap>')
             22  columns
             23  Account_ID varchar2(100) path 'Account_ID'
             24  , AccountName varchar2(100) path 'AccountName'
             25  , AccountType varchar2(30) path 'AccountType'
             26  , AccountOwner varchar2(100) path 'AccountOwner'
             27  );
             
            ACCOUNT_ID                                                                       ACCOUNTNAME                                                                      ACCOUNTTYPE                    ACCOUNTOWNER
            -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------ --------------------------------------------------------------------------------
                                                                                             XLS                                                                              Excel                          System
                                                                                             XLSS                                                                             Excel2                         System2
             
            SQL> 
            for AccountID use AccountID not Account_ID
            Account_ID varchar2(100) path 'AccountID'
            Edited by: AlexAnd on Aug 9, 2012 12:35 AM
            • 3. Re: Importing XML into oracle 11g database
              odie_63
              Hi,
              951716 wrote:
              rec.Account_ID := regexp_replace(tmp_file, '.*<Account_ID>(.*)</Account_ID>.*', '\1', 1, 1, 'n');
              rec.AccountName := regexp_replace(tmp_file, '.*<AccountName>(.*)</AccountName>.*', '\1', 1, 1, 'n');
              rec.AccountType := regexp_replace(tmp_file, '.*<AccountType >(.*)</AccountType >.*', '\1', 1, 1, 'n');
              rec.AccountOwner := regexp_replace(tmp_file, '.*<AccountOwner>(.*)</AccountOwner>.*', '\1', 1, 1, 'n');

              loop

              -- this regexp finds occurrence(s) of this pattern : "<?xml ... ?><root_tag> ... </root_tag>"
              tmp_xml := regexp_substr(tmp_file, '<\?xml[^?]+\?>\s+<([^>]+)>.*?</\1>', 1, nb_rec, 'n');
              exit when length(tmp_xml) = 0;
              --dbms_output.put_line(tmp_rec);
              nb_rec := nb_rec + 1;
              Why are you using all that stuff? You don't need that.

              You've copied some sample code I posted here recently without understanding what it does and now you're trying to use it out of context.

              Discard the whole package and simply use the query that Alex corrected.
              • 4. Re: Importing XML into oracle 11g database
                954719
                Thanks for the clarification!

                I was under the impression that I required the loop to go through all the entries in the XML file.

                The select statement is working fine but I am unable to return the contents into a table, and from my research pipeline is not a good way to do it

                I just need to simply return the output to a table now
                • 5. Re: Importing XML into oracle 11g database
                  odie_63
                  As said, a single INSERT SELECT should do it nicely.
                  Just put the following in the procedure (with the proper table and column names of course), you don't need anything else :
                  INSERT INTO target_table
                  (
                    Account_ID
                  , AccountName
                  , AccountType
                  , AccountOwner
                  )
                  SELECT Account_ID
                       , AccountName
                       , AccountType
                       , AccountOwner
                  FROM XMLTable(
                       '/AccountMap/Accounts/AccountMapping' 
                       passing xmltype(bfilename(P_DIRECTORY, P_FILENAME), nls_charset_id('AL32UTF8')) 
                       columns
                         Account_ID   varchar2(100) path 'AccountID'
                       , AccountName  varchar2(100) path 'AccountName'
                       , AccountType  varchar2(30)  path 'AccountType'
                       , AccountOwner varchar2(100) path 'AccountOwner'
                       )
                  ;
                  If you're also interested in performance, we'll advise something else.
                  • 6. Re: Importing XML into oracle 11g database
                    954719
                    Would definitely be interested to learn how I can enhance the performance.