OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » XML trouble. (Oracle 11g R1/win7)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

XML trouble. [message #452867]

Sun, 25 April 2010 13:40

Squall616
Messages: 18
Registered: April 2010
Location: Poland

Junior Member

First of all, welcome everyone Wink

I have a big problem that came up latly which is importing XML files into oracle database.
The point is that I have extracted whole PostgreSQL database into XML files - 236 tables - 1 XML file for every table and now I'm about to import them into Oracle tables.
First of all, I would like to point out that I already have the structure of all the tables in oracle database, the files only carry the data (records) that need to be imported into oracle.

I've been trying to make it running and I can't do anything more serious about it for over a week :/

I will show You all example:

insert into ps_sprawozdania(miesiac, umowa_rok, umowa_nr, nr_korekty, nazwa, data_potw, data_exp, status_potw)
 select
   extractvalue(column_value,'/NewDataSet/Cust/miesiac'),
   extractvalue(column_value,'/NewDataSet/Cust/umowa_rok'),
   extractvalue(column_value,'/NewDataSet/Cust/umowa_nr'),
   extractvalue(column_value,'/NewDataSet/Cust/nr_korekty'),
   extractvalue(column_value,'/NewDataSet/Cust/nazwa'),
   extractvalue(column_value,'/NewDataSet/Cust/data_potw'),
   extractvalue(column_value,'/NewDataSet/Cust/data_exp'),
   extractvalue(column_value,'/NewDataSet/Cust/status_potw')
from table(xmlsequence(xmltype(bfilename('c:\test\','ps_sprawozdania.xml'))));

That was one of my attempts to import data from file "ps_sprawozdania.xml" into table "ps_sprawozdnaia" into oracle.
Here are 2 records from the XML file to show you Its structure

 <NewDataSet>
 <Cust>
  <miesiac>7</miesiac> 
  <umowa_rok>2008</umowa_rok> 
  <umowa_nr>051/210412/01/000/08</umowa_nr> 
  <nr_korekty>0</nr_korekty> 
  <nazwa>Sprawozdanie z realizacji umowy nr 051/210412/01/000/08 za miesiąc Lipiec</nazwa> 
  <data_potw>2008-07-29T00:00:00+02:00</data_potw> 
  <data_exp>2008-07-29T00:00:00+02:00</data_exp> 
  <status_potw>Z</status_potw> 
  </Cust>
 <Cust>
  <miesiac>2</miesiac> 
  <umowa_rok>2007</umowa_rok> 
  <umowa_nr>051/210412/10/000/07</umowa_nr> 
  <nr_korekty>0</nr_korekty> 
  <nazwa>Sprawozdanie z realizacji umowy nr 051/210412/10/000/07 za miesiąc Luty</nazwa> 
  <status_potw>N</status_potw> 
  </Cust>
</NewDataSet>

And I've heard that this method CAN work, yet I can't make it working :/

The other way I've found on this forum is more nice and clear and I would be very grateful if anyone could help me show how to conver it to my situation.

declare 
  insCtx DBMS_XMLSave.ctxType;
  v_tablename VARCHAR2(30) := 'test' ;
  v_rows number; 
  v_record_count number; 
  java_uncaught_exception   EXCEPTION;
  PRAGMA EXCEPTION_INIT( java_uncaught_exception , -29532 );
begin
  insctx := DBMS_XMLSave.newcontext(v_tablename); 
  DBMS_XMLSave.setRowTag(insctx,'rowtest'); 
  DBMS_XMLSave.setDateFormat(insctx,'dd/MM/yyyy');
  DBMS_XMLSave.setPreserveWhitespace(insctx);
  DBMS_XMLSave.propagateoriginalexception (insCtx, true);
        v_rows := DBMS_XMLSave.insertxml(insctx,'<?xml version = ''1.0''?><test><rowtest><C1>7</C1>2<C2></C2><C3>02/08/2009</C3></rowtest><rowtest><C1>5</C1><C2>3</C2><C3>01/01/2008</C3></rowtest></test>');
  DBMS_XMLSave.closeContext(insctx);

  v_record_count := v_record_count + 1;
  dbms_output.put_line(v_rows);
  dbms_output.put_line(v_record_count);
EXCEPTION
  WHEN java_uncaught_exception THEN
  DBMS_OUTPUT.put_line (   'Error inserting to table! '
                                     || v_tablename
                                     || ' - '
                                     || SUBSTR (SQLERRM, 1, 175)
                                    );
  WHEN DUP_VAL_ON_INDEX THEN
    NULL;
  WHEN OTHERS THEN
    DBMS_OUTPUT.put_line (   'Error inserting to table '
                                     || v_tablename
                                     || ' - '
                                     || SUBSTR (SQLERRM, 1, 175)
                                    );
END;

The point is that in this example you handle with XML data, not XML files like I need to do :/

If anyone would be so kind to help me and solve this problem I would be very grateful Smile

Thanks all in advance Smile