Home » Developer & Programmer » JDeveloper, Java & XML » Loading XML in Oracle table (oracle 11g)
Loading XML in Oracle table [message #628535] Wed, 26 November 2014 02:28 Go to next message
jaggy
Messages: 15
Registered: November 2014
Location: India
Junior Member
I have one XML file and want to load it into one oracle table. Can any body suggest how to do it? I tried using SQL Loader but it is not working.
The table is having 3 columns as below. I have attached the file and given some records also.

desc temp_trade_xml
Name Null Type
---------------- ---- ------------
CUSTOMER_WHSLRNO NUMBER(38)
ID VARCHAR2(50)
TDLINK VARCHAR2(50)


Data:

<?xml version="1.0" encoding="utf-8" ?>
- <TradePrograms Version="1.0">
- <TradeProgram Name="Heavy Trade Brew Appétit - On-Premise 2015" ID="15801">
<Customer WhslrNo="11206" ID="M0496" TDLink="A-5002905" />
<Customer WhslrNo="11206" ID="M0573" TDLink="A-5002778" />
<Customer WhslrNo="11206" ID="M1242" TDLink="A-1930648" />
</TradeProgram>
</TradePrograms>

[Updated on: Wed, 26 November 2014 02:28]

Report message to a moderator

Re: Loading XML in Oracle table [message #628536 is a reply to message #628535] Wed, 26 November 2014 02:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

There are many examples in this forum.
Please search for XMLSEQUENCE or XMLTABLE.

Re: Loading XML in Oracle table [message #628537 is a reply to message #628536] Wed, 26 November 2014 02:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

For instance:
SQL> col id format a10
SQL> col tdlink format a10
SQL> with
  2    data as (
  3      select '<?xml version="1.0" encoding="utf-8" ?>
  4  <TradePrograms Version="1.0">
  5  <TradeProgram Name="Heavy Trade Brew Appétit - On-Premise 2015" ID="15801">
  6  <Customer WhslrNo="11206" ID="M0496" TDLink="A-5002905" />
  7  <Customer WhslrNo="11206" ID="M0573" TDLink="A-5002778" />
  8  <Customer WhslrNo="11206" ID="M1242" TDLink="A-1930648" />
  9  </TradeProgram>
 10  </TradePrograms>'  val from dual
 11    )
 12  select to_number(extractvalue(value(x), '/Customer/@WhslrNo')) CUSTOMER_WHSLRNO,
 13         extractvalue(value(x), '/Customer/@ID') ID,
 14         extractvalue(value(x), '/Customer/@TDLink') TDLINK
 15  from data,
 16       table(xmlsequence(extract(xmltype(val), '//Customer'))) x
 17  /
CUSTOMER_WHSLRNO ID         TDLINK
---------------- ---------- ----------
           11206 M0496      A-5002905
           11206 M0573      A-5002778
           11206 M1242      A-1930648

3 rows selected.

Re: Loading XML in Oracle table [message #628540 is a reply to message #628535] Wed, 26 November 2014 02:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Or with XML in a file:
SQL> create table t (CUSTOMER_WHSLRNO integer, id varchar2(10), tdlink varchar2(10));

Table created.

SQL> declare
  2    l_clob          clob;
  3    l_bfile         bfile := bfilename('MYDIR', 'xml2.txt');
  4    l_amount        pls_integer;
  5    l_dest_offset   pls_integer := 1;
  6    l_source_offset pls_integer := 1;
  7    l_bfile_csid    pls_integer := dbms_lob.default_csid;
  8    l_lang_context  pls_integer := dbms_lob.default_lang_ctx;
  9    l_warning       pls_integer;
 10  begin
 11    dbms_lob.createtemporary (l_clob, false);
 12    dbms_lob.open (l_bfile);
 13    l_amount := dbms_lob.getlength(l_bfile);
 14    dbms_lob.loadclobfromfile
 15      (l_clob, l_bfile, l_amount, l_dest_offset, l_source_offset,
 16       l_bfile_csid, l_lang_context, l_warning);
 17    insert into t
 18    select to_number(extractvalue(value(x), '/Customer/@WhslrNo')) CUSTOMER_WHSLRNO,
 19           extractvalue(value(x), '/Customer/@ID') ID,
 20           extractvalue(value(x), '/Customer/@TDLink') TDLINK
 21    from table(xmlsequence(extract(xmltype(l_clob), '//Customer'))) x;
 22    dbms_lob.close (l_bfile);
 23    dbms_lob.freetemporary (l_clob);
 24  end;
 25  /

PL/SQL procedure successfully completed.

SQL> select * from t;
CUSTOMER_WHSLRNO ID         TDLINK
---------------- ---------- ----------
           11206 M0496      A-5002905
           11206 M0573      A-5002778
           11206 M1242      A-1930648

3 rows selected.

Re: Loading XML in Oracle table [message #628544 is a reply to message #628540] Wed, 26 November 2014 03:17 Go to previous messageGo to next message
jaggy
Messages: 15
Registered: November 2014
Location: India
Junior Member
Hi Michel,
Thanks for your help here. Can you please let me know where you are passing the file path? When I replaced the 'MYDIR' with path it is giving me below error. I m passing like this:

l_bfile bfile := bfilename('/data/ABP/source_files/source_file_curr_extract/', 'TradeProgram.xml');

ERROR at line 1:
ORA-00972: identifier is too long
ORA-06512: at "SYS.DBMS_LOB", line 1014
ORA-06512: at line 12

[Updated on: Wed, 26 November 2014 03:18]

Report message to a moderator

Re: Loading XML in Oracle table [message #628547 is a reply to message #628544] Wed, 26 November 2014 03:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

MYDIR is an Oracle directory created using CREATE DIRECTORY statement and pointing to the OS directory.
For you:
create directory MYDIR as '/data/ABP/source_files/source_file_curr_extract';


Re: Loading XML in Oracle table [message #628614 is a reply to message #628547] Wed, 26 November 2014 07:52 Go to previous messageGo to next message
jaggy
Messages: 15
Registered: November 2014
Location: India
Junior Member
Hi Michel,
Sorry for bothering you again. I have a different set of data and tried to alter your code and load the data. But I am unable to do so. Can you please help me here.

<?xml version="1.0" encoding="utf-8" ?>
- <TradePrograms Version="1.0">
- <TradeProgram Name="Heavy Trade Brew Appétit - On-Premise 2015" ID="15801">
<Customer WhslrNo="11206" ID="M0496" TDLink="A-5002905" />
<Customer WhslrNo="11206" ID="M0573" TDLink="A-5002778" />
<Customer WhslrNo="11206" ID="M1242" TDLink="A-1930648" />
</TradeProgram>
- <TradeProgram Name="Heavy Trade 3D 2015" ID="15804">
<Customer WhslrNo="00406" ID="01010" TDLink="A-1621357" />
<Customer WhslrNo="00406" ID="01011" TDLink="A-5240940" />
<Customer WhslrNo="00406" ID="01046" TDLink="A-5218939" />
<Customer WhslrNo="00406" ID="01062" TDLink="S-0008353" />
<Customer WhslrNo="00406" ID="01096" TDLink="A-5507283" />
</TradeProgram>
- <TradeProgram Name="Heavy Trade Unlock the POC 2015" ID="15856">
<Customer WhslrNo="00338" ID="1016" TDLink="A-1395889" />
<Customer WhslrNo="00338" ID="1300" TDLink="A-1395935" />
<Customer WhslrNo="00338" ID="1530" TDLink="A-1835084" />
</TradeProgram>
</TradePrograms>

Table structure:

desc temp_trade_xml
Name Null Type
---------------- ---- ------------
TradeProgram_Name VARCHAR2(100)
program_id integer
CUSTOMER_WHSLRNO NUMBER(38)
ID VARCHAR2(50)
TDLINK VARCHAR2(50)

For each subcategry like <TradeProgram Name="Heavy Trade 3D 2015" ID="15804">( You can see one minus[-] sign in begining). We need to load the tradeprogramname and programid and the corresponding data.You can see my uploaded file for better understanding. I can see also the prefixed zeros (like in Customer WhslrNo="00406")are getting truncated when loaded to table,But we need full data.

[Updated on: Wed, 26 November 2014 08:07]

Report message to a moderator

Re: Loading XML in Oracle table [message #628624 is a reply to message #628614] Wed, 26 November 2014 08:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL> truncate table t;

Table truncated.

SQL> alter table t add (TradeProgram_Name varchar2(42), program_id integer);

Table altered.

SQL> declare
  2    l_clob          clob;
  3    l_bfile         bfile := bfilename('MYDIR', 'xml3.txt');
  4    l_amount        pls_integer;
  5    l_dest_offset   pls_integer := 1;
  6    l_source_offset pls_integer := 1;
  7    l_bfile_csid    pls_integer := dbms_lob.default_csid;
  8    l_lang_context  pls_integer := dbms_lob.default_lang_ctx;
  9    l_warning       pls_integer;
 10  begin
 11    dbms_lob.createtemporary (l_clob, false);
 12    dbms_lob.open (l_bfile);
 13    l_amount := dbms_lob.getlength(l_bfile);
 14    dbms_lob.loadclobfromfile
 15      (l_clob, l_bfile, l_amount, l_dest_offset, l_source_offset,
 16       l_bfile_csid, l_lang_context, l_warning);
 17    insert into t (TradeProgram_Name, program_id, CUSTOMER_WHSLRNO, id, tdlink)
 18    select extractvalue(value(x), '/TradeProgram/@Name') TradeProgram_Name,
 19           to_number(extractvalue(value(x), '/TradeProgram/@ID')) program_id,
 20           to_number(extractvalue(value(y), '/Customer/@WhslrNo')) CUSTOMER_WHSLRNO,
 21           extractvalue(value(y), '/Customer/@ID') ID,
 22           extractvalue(value(y), '/Customer/@TDLink') TDLINK
 23    from table(xmlsequence(extract(xmltype(l_clob), '//TradeProgram'))) x,
 24         table(xmlsequence(extract(value(x), '//Customer'))) y;
 25    dbms_lob.close (l_bfile);
 26    dbms_lob.freetemporary (l_clob);
 27  end;
 28  /

PL/SQL procedure successfully completed.

SQL> select TradeProgram_Name, program_id, CUSTOMER_WHSLRNO, id, tdlink from t;
TRADEPROGRAM_NAME                          PROGRAM_ID CUSTOMER_WHSLRNO ID         TDLINK
------------------------------------------ ---------- ---------------- ---------- ----------
Heavy Trade Brew AppÚtit - On-Premise 2015      15801            11206 M0496      A-5002905
Heavy Trade Brew AppÚtit - On-Premise 2015      15801            11206 M0573      A-5002778
Heavy Trade Brew AppÚtit - On-Premise 2015      15801            11206 M1242      A-1930648
Heavy Trade 3D 2015                             15804              406 01010      A-1621357
Heavy Trade 3D 2015                             15804              406 01011      A-5240940
Heavy Trade 3D 2015                             15804              406 01046      A-5218939
Heavy Trade 3D 2015                             15804              406 01062      S-0008353
Heavy Trade 3D 2015                             15804              406 01096      A-5507283
Heavy Trade Unlock the POC 2015                 15856              338 1016       A-1395889
Heavy Trade Unlock the POC 2015                 15856              338 1300       A-1395935
Heavy Trade Unlock the POC 2015                 15856              338 1530       A-1835084

11 rows selected.

This is the old way to do it, there is a modern solution using XMLTABLE but I'm not familiar with this function. If you wait for a couple of hours, Barbara or Solomon will come and give it.

Re: Loading XML in Oracle table [message #628636 is a reply to message #628624] Wed, 26 November 2014 08:49 Go to previous messageGo to next message
jaggy
Messages: 15
Registered: November 2014
Location: India
Junior Member
I will wait for it as the solution is not coming out. The process is running for long time and not finishing.

[Updated on: Wed, 26 November 2014 08:51]

Report message to a moderator

Re: Loading XML in Oracle table [message #628672 is a reply to message #628636] Wed, 26 November 2014 23:10 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
You can use SQL to insert the data directly into your table using BFILENAME and XMLTABLE, as demonstrated below.

-- data file:
SCOTT@orcl> HOST TYPE TradeProgram.xml
<?xml version="1.0" encoding="utf-8" ?>
<TradePrograms Version="1.0">
<TradeProgram Name="Heavy Trade Brew Appetit - On-Premise 2015" ID="15801">
<Customer WhslrNo="11206" ID="M0496" TDLink="A-5002905" />
<Customer WhslrNo="11206" ID="M0573" TDLink="A-5002778" />
<Customer WhslrNo="11206" ID="M1242" TDLink="A-1930648" />
</TradeProgram>
<TradeProgram Name="Heavy Trade 3D 2015" ID="15804">
<Customer WhslrNo="00406" ID="01010" TDLink="A-1621357" />
<Customer WhslrNo="00406" ID="01011" TDLink="A-5240940" />
<Customer WhslrNo="00406" ID="01046" TDLink="A-5218939" />
<Customer WhslrNo="00406" ID="01062" TDLink="S-0008353" />
<Customer WhslrNo="00406" ID="01096" TDLink="A-5507283" />
</TradeProgram>
<TradeProgram Name="Heavy Trade Unlock the POC 2015" ID="15856">
<Customer WhslrNo="00338" ID="1016" TDLink="A-1395889" />
<Customer WhslrNo="00338" ID="1300" TDLink="A-1395935" />
<Customer WhslrNo="00338" ID="1530" TDLink="A-1835084" />
</TradeProgram>
</TradePrograms>


-- table:
SCOTT@orcl> CREATE TABLE temp_trade_xml
  2    (TradeProgram_Name  VARCHAR2(100),
  3     program_id         integer,
  4     CUSTOMER_WHSLRNO   NUMBER(38),
  5     ID                 VARCHAR2(50),
  6     TDLINK             VARCHAR2(50))
  7  /

Table created.


-- Oracle directory object (using path on my system):
SCOTT@orcl> CREATE OR REPLACE DIRECTORY mydir AS 'c:\my_oracle_files'
  2  /

Directory created.


-- insert statement:
SCOTT@orcl> INSERT INTO temp_trade_xml
  2    (TradeProgram_name, program_id, customer_whslrno, id, tdlink)
  3  SELECT x.TradeProgram_name, x.program_id,
  4         y.customer_whslrno, y.id, y.tdlink
  5  FROM   XMLTABLE
  6           ('//TradeProgram'
  7            PASSING XMLTYPE (BFILENAME ('MYDIR', 'TradeProgram.xml'), NLS_CHARSET_ID ('AL32UTF8'))
  8            COLUMNS
  9              TradeProgram_Name  VARCHAR2(100)  PATH '@Name',
 10              program_id         INTEGER        PATH '@ID',
 11              customers          XMLTYPE        PATH '/TradeProgram') x,
 12         XMLTABLE
 13           ('//Customer'
 14            PASSING x.customers
 15            COLUMNS
 16              customer_whslrno   NUMBER(38)     PATH '@WhslrNo',
 17              id                 VARCHAR2(50)   PATH '@ID',
 18              tdlink             VARCHAR2(50)   PATH '@TDLink') y
 19  /

11 rows created.


-- resutls:
SCOTT@orcl> COLUMN tradeprogram_name FORMAT A42
SCOTT@orcl> COLUMN id                FORMAT A5
SCOTT@orcl> COLUMN tdlink            FORMAT A9
SCOTT@orcl> SELECT * FROM temp_trade_xml
  2  /

TRADEPROGRAM_NAME                          PROGRAM_ID CUSTOMER_WHSLRNO ID    TDLINK
------------------------------------------ ---------- ---------------- ----- ---------
Heavy Trade Brew Appetit - On-Premise 2015      15801            11206 M0496 A-5002905
Heavy Trade Brew Appetit - On-Premise 2015      15801            11206 M0573 A-5002778
Heavy Trade Brew Appetit - On-Premise 2015      15801            11206 M1242 A-1930648
Heavy Trade 3D 2015                             15804              406 01010 A-1621357
Heavy Trade 3D 2015                             15804              406 01011 A-5240940
Heavy Trade 3D 2015                             15804              406 01046 A-5218939
Heavy Trade 3D 2015                             15804              406 01062 S-0008353
Heavy Trade 3D 2015                             15804              406 01096 A-5507283
Heavy Trade Unlock the POC 2015                 15856              338 1016  A-1395889
Heavy Trade Unlock the POC 2015                 15856              338 1300  A-1395935
Heavy Trade Unlock the POC 2015                 15856              338 1530  A-1835084

11 rows selected.


Note: I had to remove the accent from Appétit to insert it onto my system.

[Updated on: Wed, 26 November 2014 23:15]

Report message to a moderator

Re: Loading XML in Oracle table [message #628709 is a reply to message #628535] Thu, 27 November 2014 04:50 Go to previous messageGo to next message
jaggy
Messages: 15
Registered: November 2014
Location: India
Junior Member
77
Re: Loading XML in Oracle table [message #628712 is a reply to message #628709] Thu, 27 November 2014 05:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Question
Re: Loading XML in Oracle table [message #634243 is a reply to message #628672] Fri, 06 March 2015 06:04 Go to previous messageGo to next message
jaggy
Messages: 15
Registered: November 2014
Location: India
Junior Member
Hi ,
I have a different XML file and want to load into 2 tables. One part of the file should be loaded to one table and the subtags to another table.I tried altering the code as below to load the file.But it is loading with 0 records.
can somebody help here where it is going wrong


create table temp_account_map (
NAME VARCHAR2(100),
PROGRAM_ID NUMBER(38) ,
PROGRAMNAME VARCHAR2(100),
YEAR integer,
MappedProgramID integer,
Attachment_ID integer,
Attachment_SeqNo integer,
Attachment_Path VARCHAR2(500),
Attachment_Name VARCHAR2(500),
Attachment_FileType VARCHAR2(500));

create table temp_account_2 (
NAME VARCHAR2(100),
PROGRAM_ID NUMBER(38) ,
PROGRAMNAME VARCHAR2(100),
YEAR integer,
CUSTOMER_WHSLRNO NUMBER(38) ,
ID VARCHAR2(50) ,
TDLINK VARCHAR2(50) ,
RETAILERPARTYID NUMBER(38));

I m trying with below code to load into one table. But no luck.
 INSERT INTO temp_account_map
   (Name, program_id,ProgramName,Year,MappedProgramID,Attachment_ID,Attachment_SeqNo,Attachment_Path,Attachment_Name,Attachment_FileType)
 SELECT x.Name, x.program_id,x.ProgramName,x.Year,
        y.MappedProgramID,
		z.Attachment_ID,z.Attachment_SeqNo,z.Attachment_Path,z.Attachment_Name,z.Attachment_FileType
 FROM   XMLTABLE
          ('//TradeProgram'
           PASSING XMLTYPE (BFILENAME ('MY_DIR', 'TradeProgramAccounts_New_Format.xml'), NLS_CHARSET_ID ('AL32UTF8'))
           COLUMNS
             Name               VARCHAR2(100)  PATH '@Name',
             program_id         INTEGER        PATH '@ID',
             ProgramName        VARCHAR2(100)  PATH '@ProgramName',
			 Year               NUMBER(38)     PATH '@Year',
             ProgramMappings    XMLTYPE        PATH '/ProgramMappings') x,
        XMLTABLE
          ('//MappedProgramID'
           PASSING x.ProgramMappings
           COLUMNS
             MappedProgramID   NUMBER(38)     PATH '@MappedProgramID',
			 Attachments       XMLTYPE        PATH '/Attachments') y,			 
		XMLTABLE
          ('//Attachments'
           PASSING y.Attachments
           COLUMNS
             Attachment_ID           NUMBER(38)      PATH '@ID',
             Attachment_SeqNo        NUMBER(38)      PATH '@SeqNo',
             Attachment_Path         VARCHAR2(500)   PATH '@Path',
			 Attachment_Name         VARCHAR2(500)   PATH '@Name',
			 Attachment_FileType     VARCHAR2(500)   PATH '@FileType') z
             /


Sample File:
<?xml version="1.0" encoding="utf-8"?>
<TradePrograms Version="1.0">
 <TradeProgram Name="Brew Appetit" ID="15801" ProgramName="Heavy Trade Brew Appetit - On-Premise 2015" Year="2015">
  <ProgramMappings>
   <ProgramMapping MappedProgramID="15801" />
   <ProgramMapping MappedProgramID="15945" />
   <ProgramMapping MappedProgramID="18794" />
  </ProgramMappings>
  <Attachments>
   <Attachment ID="1" SeqNo="1" Path="\POCPlanning\TradeProgram" Name="ba_txt.png" FileType="I" />
   <Attachment ID="6" SeqNo="1" Path="\POCPlanning\TradeProgram" Name="Brew Appetit 1 pager.pdf" />
  </Attachments>
  <Customer WhslrNo="04163" ID="00029" TDLink="A-2354765-018311-02760" RetailerPartyId="171727405" />
  <Customer WhslrNo="04163" ID="00475" TDLink="A-3097786-018331-02800" RetailerPartyId="227206761" />
  <Customer WhslrNo="04163" ID="00556" TDLink="A-1907543-012698-01333" RetailerPartyId="6332910" />
  <Customer WhslrNo="94262" ID="43299" TDLink="A-2601020" RetailerPartyId="82958013" />
 </TradeProgram>
  <TradeProgram Name="3D" ID="15804" ProgramName="Heavy Trade 3D 2015" Year="2015">
  <ProgramMappings>
   <ProgramMapping MappedProgramID="15804" />
   <ProgramMapping MappedProgramID="15943" />
   <ProgramMapping MappedProgramID="18602" />
  </ProgramMappings>
  <Attachments>
   <Attachment ID="2" SeqNo="1" Path="\POCPlanning\TradeProgram" Name="3d_txt.png" FileType="I" />
   <Attachment ID="7" SeqNo="1" Path="\POCPlanning\TradeProgram" Name="3D 1 pager.pdf" />
  </Attachments>
  <Customer WhslrNo="00177" ID="10019" TDLink="A-5563342" RetailerPartyId="6206854" />
  <Customer WhslrNo="00177" ID="10069" TDLink="A-5549534" RetailerPartyId="6703033" />
    <Customer WhslrNo="94682" ID="860" TDLink="A-5616008" RetailerPartyId="5686571" />
 </TradeProgram>
 <TradeProgram Name="Rock The POC" ID="15863" ProgramName="Heavy Trade Rock the POC 2015" Year="2015">
  <ProgramMappings>
   <ProgramMapping MappedProgramID="15863" />
   <ProgramMapping MappedProgramID="15947" />
   <ProgramMapping MappedProgramID="16503" />
  </ProgramMappings>
  <Attachments>
   <Attachment ID="5" SeqNo="1" Path="\POCPlanning\TradeProgram" Name="rtp_txt.png" FileType="I" />
   <Attachment ID="10" SeqNo="1" Path="\POCPlanning\TradeProgram" Name="RTP 1 pager.pdf" />
  </Attachments>
  <Customer WhslrNo="00177" ID="10003" TDLink="A-0643008" RetailerPartyId="6507389" />
  <Customer WhslrNo="00177" ID="10026" TDLink="A-1668515" RetailerPartyId="5672505" />
  <Customer WhslrNo="00177" ID="10054" TDLink="A-0747853" RetailerPartyId="6716644" />
    <Customer WhslrNo="94682" ID="976" TDLink="A-0641268" RetailerPartyId="163809104" />
 </TradeProgram>
</TradePrograms>
Re: Loading XML in Oracle table [message #634258 is a reply to message #634243] Fri, 06 March 2015 10:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please post the result that should be in each table and tell from which node comes each column of the tables.

Re: Loading XML in Oracle table [message #634264 is a reply to message #634243] Fri, 06 March 2015 15:47 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Please see the corrected code below that inserts into one of your tables.

SCOTT@orcl12c> INSERT INTO temp_account_map
  2  	(Name, program_id,ProgramName,Year,MappedProgramID,Attachment_ID,Attachment_SeqNo,Attachment_Path,Attachment_Name,Attachment_FileType)
  3  SELECT x.Name, x.program_id,x.ProgramName,x.Year,
  4  	    y.MappedProgramID,
  5  	    z.Attachment_ID,z.Attachment_SeqNo,z.Attachment_Path,z.Attachment_Name,z.Attachment_FileType
  6  FROM   XMLTABLE
  7  	       ('//TradeProgram'
  8  		PASSING XMLTYPE (BFILENAME ('MY_DIR', 'TradeProgramAccounts_New_Format.xml'), NLS_CHARSET_ID ('AL32UTF8'))
  9  		COLUMNS
 10  		  Name		     VARCHAR2(100)  PATH '@Name',
 11  		  program_id	     INTEGER	    PATH '@ID',
 12  		  ProgramName	     VARCHAR2(100)  PATH '@ProgramName',
 13  		  Year		     NUMBER(38)     PATH '@Year',
 14  		  ProgramMappings    XMLTYPE	    PATH '/ProgramMappings',
 15  		  Attachments	     XMLTYPE	    PATH '/Attachments') x,
 16  	     XMLTABLE
 17  	       ('//ProgramMapping'
 18  		PASSING x.ProgramMappings
 19  		COLUMNS
 20  		MappedProgramID   NUMBER(38)	 PATH '@MappedProgramID') y,
 21  	     XMLTABLE
 22  	       ('//Attachment'
 23  		PASSING x.Attachments
 24  		COLUMNS
 25  		  Attachment_ID 	  NUMBER(38)	  PATH '@ID',
 26  		  Attachment_SeqNo	  NUMBER(38)	  PATH '@SeqNo',
 27  		  Attachment_Path	  VARCHAR2(500)   PATH '@Path',
 28  		  Attachment_Name	  VARCHAR2(500)   PATH '@Name',
 29  		  Attachment_FileType	  VARCHAR2(500)   PATH '@FileType') z
 30  /

18 rows created.

Re: Loading XML in Oracle table [message #634266 is a reply to message #634264] Fri, 06 March 2015 16:15 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
And for the other table:

SCOTT@orcl12c> INSERT INTO temp_account_2
  2  	(Name, program_id,ProgramName,Year,
  3  	       customer_whslrno, id, tdlink, retailerpartyid)
  4  SELECT x.Name, x.program_id,x.ProgramName,x.Year,
  5  	    y.WhslrNo, y.ID, y.TDLink, y.RetailerPartyId
  6  FROM   XMLTABLE
  7  	       ('//TradeProgram'
  8  		PASSING XMLTYPE (BFILENAME ('MY_DIR', 'TradeProgramAccounts_New_Format.xml'), NLS_CHARSET_ID ('AL32UTF8'))
  9  		COLUMNS
 10  		  Name		     VARCHAR2(100)  PATH '@Name',
 11  		  program_id	     INTEGER	    PATH '@ID',
 12  		  ProgramName	     VARCHAR2(100)  PATH '@ProgramName',
 13  		  Year		     NUMBER(38)     PATH '@Year',
 14  		  Customer	     XMLTYPE	    PATH '//Customer') x,
 15  	    XMLTABLE
 16  	       ('//Customer'
 17  		PASSING x.Customer
 18  		COLUMNS
 19  		  WhslrNo	     VARCHAR2(100)  PATH '@WhslrNo',
 20  		  ID		     VARCHAR2(100)  PATH '@ID',
 21  		  TDLink	     VARCHAR2(100)  PATH '@TDLink',
 22  		  RetailerPartyId    VARCHAR2(100)  PATH '@RetailerPartyId') y
 23  /

11 rows created.

Re: Loading XML in Oracle table [message #634296 is a reply to message #634264] Sat, 07 March 2015 02:08 Go to previous messageGo to next message
jaggy
Messages: 15
Registered: November 2014
Location: India
Junior Member
Hi,
Have you done any changes to file? Because when I am trying to load with the code provided by you and the same file also,It is loading 0 records.
INSERT INTO TEMP_ACCOUNT_MAP
        (Name, program_id,ProgramName,Year,MappedProgramID,Attachment_ID,Attachment_SeqNo,Attachment_Path,Attachment_Name,Attachment_FileType)
  SELECT x.Name, x.program_id,x.ProgramName,x.Year,
            y.MappedProgramID,
            z.Attachment_ID,z.Attachment_SeqNo,z.Attachment_Path,z.Attachment_Name,z.Attachment_FileType
  FROM   XMLTABLE
               ('//TradeProgram'
                PASSING XMLTYPE (BFILENAME ('APTT_DATA_EXPORT', 'new1.XML'), NLS_CHARSET_ID ('AL32UTF8'))
                COLUMNS
                  Name               VARCHAR2(100)  PATH '@Name',
                  program_id          NUMBER(38)            PATH '@ID',
                  ProgramName        VARCHAR2(100)  PATH '@ProgramName',
                  Year               NUMBER(38)     PATH '@Year',
                  ProgramMappings    XMLTYPE        PATH '/ProgramMappings',
                  Attachments        XMLTYPE        PATH '/Attachments') x,
             XMLTABLE
               ('//ProgramMapping'
                PASSING x.ProgramMappings
                COLUMNS
                MappedProgramID   NUMBER(38)     PATH '@MappedProgramID') y,
             XMLTABLE
               ('//Attachment'
                PASSING x.Attachments
                COLUMNS
                  Attachment_ID           NUMBER(38)      PATH '@ID',
                  Attachment_SeqNo        NUMBER(38)      PATH '@SeqNo',
                  Attachment_Path         VARCHAR2(500)   PATH '@Path',
                  Attachment_Name         VARCHAR2(500)   PATH '@Name',
                  Attachment_FileType     VARCHAR2(500)   PATH '@FileType') z
  /

0 rows created.

commit;

Commit complete.
Re: Loading XML in Oracle table [message #634301 is a reply to message #634296] Sat, 07 March 2015 03:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Double lone '/' in first XMLTABLE.

Re: Loading XML in Oracle table [message #634305 is a reply to message #634301] Sat, 07 March 2015 03:09 Go to previous messageGo to next message
jaggy
Messages: 15
Registered: November 2014
Location: India
Junior Member
Sorry,I did not get it..
Re: Loading XML in Oracle table [message #634307 is a reply to message #634305] Sat, 07 March 2015 03:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I mean '/ProgramMappings' should be '//ProgramMappings' (or /TradeProgram/ProgramMappings)...

Re: Loading XML in Oracle table [message #634310 is a reply to message #634296] Sat, 07 March 2015 04:05 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
I did not change the file. However, I see that you have changed the directory name and file name. In the following, I used the same file, but changed the directory name and file name to match your new code, then copied and pasted your code, with a little formatting, and ran it. As you can see, it works for me. So, either you are using a different file or there is something different between our systems. It is also possible that posting the file inline causes some changes to the file. Please post your data file as an attachment, so that I can download it without change and test it.

SCOTT@orcl12c> HOST TYPE new1.xml
<?xml version="1.0" encoding="utf-8"?>
<TradePrograms Version="1.0">
<TradeProgram Name="Brew Appetit" ID="15801" ProgramName="Heavy Trade Brew Appetit - On-Premise 2015" Year="2015">
<ProgramMappings>
<ProgramMapping MappedProgramID="15801" />
<ProgramMapping MappedProgramID="15945" />
<ProgramMapping MappedProgramID="18794" />
</ProgramMappings>
<Attachments>
<Attachment ID="1" SeqNo="1" Path="\POCPlanning\TradeProgram" Name="ba_txt.png" FileType="I" />
<Attachment ID="6" SeqNo="1" Path="\POCPlanning\TradeProgram" Name="Brew Appetit 1 pager.pdf" />
</Attachments>
<Customer WhslrNo="04163" ID="00029" TDLink="A-2354765-018311-02760" RetailerPartyId="171727405" />
<Customer WhslrNo="04163" ID="00475" TDLink="A-3097786-018331-02800" RetailerPartyId="227206761" />
<Customer WhslrNo="04163" ID="00556" TDLink="A-1907543-012698-01333" RetailerPartyId="6332910" />
<Customer WhslrNo="94262" ID="43299" TDLink="A-2601020" RetailerPartyId="82958013" />
</TradeProgram>
<TradeProgram Name="3D" ID="15804" ProgramName="Heavy Trade 3D 2015" Year="2015">
<ProgramMappings>
<ProgramMapping MappedProgramID="15804" />
<ProgramMapping MappedProgramID="15943" />
<ProgramMapping MappedProgramID="18602" />
</ProgramMappings>
<Attachments>
<Attachment ID="2" SeqNo="1" Path="\POCPlanning\TradeProgram" Name="3d_txt.png" FileType="I" />
<Attachment ID="7" SeqNo="1" Path="\POCPlanning\TradeProgram" Name="3D 1 pager.pdf" />
</Attachments>
<Customer WhslrNo="00177" ID="10019" TDLink="A-5563342" RetailerPartyId="6206854" />
<Customer WhslrNo="00177" ID="10069" TDLink="A-5549534" RetailerPartyId="6703033" />
<Customer WhslrNo="94682" ID="860" TDLink="A-5616008" RetailerPartyId="5686571" />
</TradeProgram>
<TradeProgram Name="Rock The POC" ID="15863" ProgramName="Heavy Trade Rock the POC 2015" Year="2015">
<ProgramMappings>
<ProgramMapping MappedProgramID="15863" />
<ProgramMapping MappedProgramID="15947" />
<ProgramMapping MappedProgramID="16503" />
</ProgramMappings>
<Attachments>
<Attachment ID="5" SeqNo="1" Path="\POCPlanning\TradeProgram" Name="rtp_txt.png" FileType="I" />
<Attachment ID="10" SeqNo="1" Path="\POCPlanning\TradeProgram" Name="RTP 1 pager.pdf" />
</Attachments>
<Customer WhslrNo="00177" ID="10003" TDLink="A-0643008" RetailerPartyId="6507389" />
<Customer WhslrNo="00177" ID="10026" TDLink="A-1668515" RetailerPartyId="5672505" />
<Customer WhslrNo="00177" ID="10054" TDLink="A-0747853" RetailerPartyId="6716644" />
<Customer WhslrNo="94682" ID="976" TDLink="A-0641268" RetailerPartyId="163809104" />
</TradeProgram>
</TradePrograms>

SCOTT@orcl12c> create table temp_account_map (
  2  NAME VARCHAR2(100),
  3  PROGRAM_ID NUMBER(38) ,
  4  PROGRAMNAME VARCHAR2(100),
  5  YEAR integer,
  6  MappedProgramID integer,
  7  Attachment_ID integer,
  8  Attachment_SeqNo integer,
  9  Attachment_Path VARCHAR2(500),
 10  Attachment_Name VARCHAR2(500),
 11  Attachment_FileType VARCHAR2(500))
 12  /

Table created.

SCOTT@orcl12c> CREATE OR REPLACE DIRECTORY APTT_DATA_EXPORT AS 'c:\my_oracle_files'
  2  /

Directory created.

SCOTT@orcl12c> INSERT INTO TEMP_ACCOUNT_MAP
  2    (Name, program_id,ProgramName,Year,
  3     MappedProgramID,
  4     Attachment_ID,Attachment_SeqNo,Attachment_Path,Attachment_Name,Attachment_FileType)
  5  SELECT x.Name, x.program_id,x.ProgramName,x.Year,
  6         y.MappedProgramID,
  7         z.Attachment_ID,z.Attachment_SeqNo,z.Attachment_Path,z.Attachment_Name,z.Attachment_FileType
  8  FROM   XMLTABLE
  9           ('//TradeProgram'
 10             PASSING XMLTYPE (BFILENAME ('APTT_DATA_EXPORT', 'new1.XML'), NLS_CHARSET_ID ('AL32UTF8'))
 11             COLUMNS
 12               Name                    VARCHAR2(100)   PATH '@Name',
 13               program_id              NUMBER(38)      PATH '@ID',
 14               ProgramName             VARCHAR2(100)   PATH '@ProgramName',
 15               Year                    NUMBER(38)      PATH '@Year',
 16               ProgramMappings         XMLTYPE         PATH '/ProgramMappings',
 17               Attachments             XMLTYPE         PATH '/Attachments') x,
 18         XMLTABLE
 19           ('//ProgramMapping'
 20            PASSING x.ProgramMappings
 21            COLUMNS
 22              MappedProgramID          NUMBER(38)      PATH '@MappedProgramID') y,
 23         XMLTABLE
 24           ('//Attachment'
 25            PASSING x.Attachments
 26            COLUMNS
 27              Attachment_ID            NUMBER(38)      PATH '@ID',
 28              Attachment_SeqNo         NUMBER(38)      PATH '@SeqNo',
 29              Attachment_Path          VARCHAR2(500)   PATH '@Path',
 30              Attachment_Name          VARCHAR2(500)   PATH '@Name',
 31              Attachment_FileType      VARCHAR2(500)   PATH '@FileType') z
 32  /

18 rows created.

SCOTT@orcl12c> 

Re: Loading XML in Oracle table [message #634313 is a reply to message #634310] Sat, 07 March 2015 04:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I copied and pasted OP's file in the same file and directory names than yours ('MY_DIR', 'TradeProgramAccounts_New_Format.xml') and I got the same result than OP.
When I used the 2 syntax I mentioned, I got the rows.
Maybe it is a version specific issue, I used 11.2.0.1 for this test.

Re: Loading XML in Oracle table [message #634315 is a reply to message #634313] Sat, 07 March 2015 04:26 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Michel,

I was previously testing on 12.1.0.1.0. After reading your latest post, I tested on my other database, which is 11.2.0.1.0 like yours. I got 0 rows. I then tried both of your suggestions and still got 0 rows. So, it does appear to be a version specific problem and more. Perhaps the operating system has some effect as well. I am using Windows. Hopefully, the OP has better luck and your suggestions work on his system. I am still fiddling with it on my system, trying to get it to work on 11g.
Re: Loading XML in Oracle table [message #634316 is a reply to message #634315] Sat, 07 March 2015 04:34 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
I just needed to double the / for the attachments as well.

SCOTT@orcl> SELECT banner FROM v$version
  2  /

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

5 rows selected.

SCOTT@orcl> HOST TYPE new1.xml
<?xml version="1.0" encoding="utf-8"?>
<TradePrograms Version="1.0">
<TradeProgram Name="Brew Appetit" ID="15801" ProgramName="Heavy Trade Brew Appetit - On-Premise 2015" Year="2015">
<ProgramMappings>
<ProgramMapping MappedProgramID="15801" />
<ProgramMapping MappedProgramID="15945" />
<ProgramMapping MappedProgramID="18794" />
</ProgramMappings>
<Attachments>
<Attachment ID="1" SeqNo="1" Path="\POCPlanning\TradeProgram" Name="ba_txt.png" FileType="I" />
<Attachment ID="6" SeqNo="1" Path="\POCPlanning\TradeProgram" Name="Brew Appetit 1 pager.pdf" />
</Attachments>
<Customer WhslrNo="04163" ID="00029" TDLink="A-2354765-018311-02760" RetailerPartyId="171727405" />
<Customer WhslrNo="04163" ID="00475" TDLink="A-3097786-018331-02800" RetailerPartyId="227206761" />
<Customer WhslrNo="04163" ID="00556" TDLink="A-1907543-012698-01333" RetailerPartyId="6332910" />
<Customer WhslrNo="94262" ID="43299" TDLink="A-2601020" RetailerPartyId="82958013" />
</TradeProgram>
<TradeProgram Name="3D" ID="15804" ProgramName="Heavy Trade 3D 2015" Year="2015">
<ProgramMappings>
<ProgramMapping MappedProgramID="15804" />
<ProgramMapping MappedProgramID="15943" />
<ProgramMapping MappedProgramID="18602" />
</ProgramMappings>
<Attachments>
<Attachment ID="2" SeqNo="1" Path="\POCPlanning\TradeProgram" Name="3d_txt.png" FileType="I" />
<Attachment ID="7" SeqNo="1" Path="\POCPlanning\TradeProgram" Name="3D 1 pager.pdf" />
</Attachments>
<Customer WhslrNo="00177" ID="10019" TDLink="A-5563342" RetailerPartyId="6206854" />
<Customer WhslrNo="00177" ID="10069" TDLink="A-5549534" RetailerPartyId="6703033" />
<Customer WhslrNo="94682" ID="860" TDLink="A-5616008" RetailerPartyId="5686571" />
</TradeProgram>
<TradeProgram Name="Rock The POC" ID="15863" ProgramName="Heavy Trade Rock the POC 2015" Year="2015">
<ProgramMappings>
<ProgramMapping MappedProgramID="15863" />
<ProgramMapping MappedProgramID="15947" />
<ProgramMapping MappedProgramID="16503" />
</ProgramMappings>
<Attachments>
<Attachment ID="5" SeqNo="1" Path="\POCPlanning\TradeProgram" Name="rtp_txt.png" FileType="I" />
<Attachment ID="10" SeqNo="1" Path="\POCPlanning\TradeProgram" Name="RTP 1 pager.pdf" />
</Attachments>
<Customer WhslrNo="00177" ID="10003" TDLink="A-0643008" RetailerPartyId="6507389" />
<Customer WhslrNo="00177" ID="10026" TDLink="A-1668515" RetailerPartyId="5672505" />
<Customer WhslrNo="00177" ID="10054" TDLink="A-0747853" RetailerPartyId="6716644" />
<Customer WhslrNo="94682" ID="976" TDLink="A-0641268" RetailerPartyId="163809104" />
</TradeProgram>
</TradePrograms>

SCOTT@orcl> create table temp_account_map (
  2  NAME VARCHAR2(100),
  3  PROGRAM_ID NUMBER(38) ,
  4  PROGRAMNAME VARCHAR2(100),
  5  YEAR integer,
  6  MappedProgramID integer,
  7  Attachment_ID integer,
  8  Attachment_SeqNo integer,
  9  Attachment_Path VARCHAR2(500),
 10  Attachment_Name VARCHAR2(500),
 11  Attachment_FileType VARCHAR2(500))
 12  /

Table created.

SCOTT@orcl> CREATE OR REPLACE DIRECTORY APTT_DATA_EXPORT AS 'c:\my_oracle_files'
  2  /

Directory created.

SCOTT@orcl> INSERT INTO TEMP_ACCOUNT_MAP
  2    (Name, program_id,ProgramName,Year,
  3     MappedProgramID,
  4     Attachment_ID,Attachment_SeqNo,Attachment_Path,Attachment_Name,Attachment_FileType)
  5  SELECT x.Name, x.program_id,x.ProgramName,x.Year,
  6         y.MappedProgramID,
  7         z.Attachment_ID,z.Attachment_SeqNo,z.Attachment_Path,z.Attachment_Name,z.Attachment_FileType
  8  FROM   XMLTABLE
  9           ('//TradeProgram'
 10             PASSING XMLTYPE (BFILENAME ('APTT_DATA_EXPORT', 'new1.XML'), NLS_CHARSET_ID ('AL32UTF8'))
 11             COLUMNS
 12               Name                    VARCHAR2(100)   PATH '@Name',
 13               program_id              NUMBER(38)      PATH '@ID',
 14               ProgramName             VARCHAR2(100)   PATH '@ProgramName',
 15               Year                    NUMBER(38)      PATH '@Year',
 16               ProgramMappings         XMLTYPE         PATH '//ProgramMappings',
 17               Attachments             XMLTYPE         PATH '//Attachments') x,
 18         XMLTABLE
 19           ('//ProgramMapping'
 20            PASSING x.ProgramMappings
 21            COLUMNS
 22              MappedProgramID          NUMBER(38)      PATH '@MappedProgramID') y,
 23         XMLTABLE
 24           ('//Attachment'
 25            PASSING x.Attachments
 26            COLUMNS
 27              Attachment_ID            NUMBER(38)      PATH '@ID',
 28              Attachment_SeqNo         NUMBER(38)      PATH '@SeqNo',
 29              Attachment_Path          VARCHAR2(500)   PATH '@Path',
 30              Attachment_Name          VARCHAR2(500)   PATH '@Name',
 31              Attachment_FileType      VARCHAR2(500)   PATH '@FileType') z
 32  /

18 rows created.

SCOTT@orcl> 

Re: Loading XML in Oracle table [message #634319 is a reply to message #634315] Sat, 07 March 2015 04:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Perhaps the operating system has some effect as well. I am using Windows.


I also thought about that remembering we had the same output (forma) some time ago.
My OS is an old Windows XP SP3 Pro.

Quote:
I just needed to double the / for the attachments as well.


Yes, this is what I mentioned in one of my posts, maybe you didn't see it; the next post shows an example.

The fact it works for you with the previous syntax is one of Oracle mysteries (bug?). Smile

Re: Loading XML in Oracle table [message #656255 is a reply to message #634307] Fri, 30 September 2016 08:42 Go to previous message
jaggy
Messages: 15
Registered: November 2014
Location: India
Junior Member
Thanks for your valuable solution..
Previous Topic: List all Tag and Field Names in XML
Next Topic: ORA-01780 string literal required error while fetching data from xml table.
Goto Forum:
  


Current Time: Thu Mar 28 18:25:48 CDT 2024