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  |
 |
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 #628537 is a reply to message #628536] |
Wed, 26 November 2014 02:37  |
 |
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
|
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
|
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 #628614 is a reply to message #628547] |
Wed, 26 November 2014 07:52 |
|
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 |
|
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 #628672 is a reply to message #628636] |
Wed, 26 November 2014 23:10 |
 |
Barbara Boehmer
Messages: 9104
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 #634243 is a reply to message #628672] |
Fri, 06 March 2015 06:04 |
|
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 #634264 is a reply to message #634243] |
Fri, 06 March 2015 15:47 |
|
Barbara Boehmer
Messages: 9104
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 |
|
Barbara Boehmer
Messages: 9104
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 |
|
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 #634310 is a reply to message #634296] |
Sat, 07 March 2015 04:05 |
|
Barbara Boehmer
Messages: 9104
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 #634316 is a reply to message #634315] |
Sat, 07 March 2015 04:34 |
|
Barbara Boehmer
Messages: 9104
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>
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Mon Feb 03 17:42:26 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
