This discussion is archived
6 Replies Latest reply: Aug 10, 2012 8:43 AM by 954719 RSS

Importing XML into oracle 11g database

954719 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Would definitely be interested to learn how I can enhance the performance.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points