Home » RDBMS Server » Server Utilities » xml file with same segment name loading into database (Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production)
| xml file with same segment name loading into database [message #613552] |
Wed, 07 May 2014 15:57  |
jamalfarooq
Messages: 35
Registered: August 2006
|
Member |
|
|
Hi Gurus,
I search many forum but could not get the same example which match my critaria. I have xml file with same segment name and I need to load it into database by using sql loader.
Sample XML:
<?xml version="1.0"?>
<index_file>
<header>
<version>1.0</version>
<customer_name>CHQ_FT_Test</customer_name>
<todo_group_name>Daily-CFT-ASCU</todo_group_name>
<extraction_name>CHQ-FT-Extract</extraction_name>
<translation_name>CHQ-FT-TranslateToTiff</translation_name>
<scheduled_date>20060602</scheduled_date>
<extract_period_start>20060602</extract_period_start>
<extract_period_end>20060602</extract_period_end>
<text_encoding>ascii</text_encoding>
<content_type>cheque</content_type>
<checksum_field_name>Amount</checksum_field_name>
<data_file_name> PSYM.CHCK.C9998.DATA.D070418.T135513.C0101 </data_file_name>
<data_file_name_2>cccccccc</data_file_name_2>
</header>
<body>
<item>
<field>
<field_name>ProcessingDate</field_name>
<field_value>20060602</field_value>
</field>
<field>
<field_name>ChequeSerialNumber</field_name>
<field_value>112</field_value>
</field>
<field>
<field_name>Amount</field_name>
<field_value>33333333.33</field_value>
</field>
<field>
<field_name>DRCRIndicator</field_name>
<field_value>D</field_value>
</field>
<field>
<field_name>Currency</field_name>
<field_value>CAD</field_value>
</field>
<field>
<field_name>TransactionType</field_name>
<field_value>0</field_value>
</field>
<field>
<field_name>ItemSequenceNumber</field_name>
<field_value>500163174</field_value>
</field>
<field>
<field_name>RoutingTransit</field_name>
<field_value>21991001</field_value>
</field>
<field>
<field_name>Account</field_name>
<field_value>1032785</field_value>
</field>
<field>
<field_name>CaptureSite</field_name>
<field_value>TO</field_value>
</field>
<data_offset>0</data_offset>
<data_length>23303</data_length>
</item>
<item>
<field>
<field_name>ProcessingDate</field_name>
<field_value>20060602</field_value>
</field>
<field>
<field_name>ChequeSerialNumber</field_name>
<field_value>111</field_value>
</field>
<field>
<field_name>Amount</field_name>
<field_value>22222222.22</field_value>
</field>
<field>
<field_name>DRCRIndicator</field_name>
<field_value>D</field_value>
</field>
<field>
<field_name>Currency</field_name>
<field_value>CAD</field_value>
</field>
<field>
<field_name>TransactionType</field_name>
<field_value>0</field_value>
</field>
<field>
<field_name>ItemSequenceNumber</field_name>
<field_value>500163173</field_value>
</field>
<field>
<field_name>RoutingTransit</field_name>
<field_value>21991001</field_value>
</field>
<field>
<field_name>Account</field_name>
<field_value>1032785</field_value>
</field>
<field>
<field_name>CaptureSite</field_name>
<field_value>TO</field_value>
</field>
<data_offset>23303</data_offset>
<data_length>23272</data_length>
</item>
</body>
<trailer>
<item_count>20</item_count>
<checksum>
<checksum_field_name>Amount</checksum_field_name>
<checksum_field_value>512345773.75</checksum_field_value>
</checksum>
</trailer>
</index_file>
Table Structure:
create table xml_test
(version varchar2(10),
customer_name varchar2(50),
todo_group_name varchar2(50),
extraction_name varchar2(50),
translation_name varchar2(50),
scheduled_date date,
extract_period_start date,
extract_period_end date,
text_encoding varchar2(50),
content_type varchar2(50),
checksum_field_name varchar2(50),
data_file_name varchar2(100),
data_file_name_2 varchar2(100),
ProcessingDate date,
ChequeSerialNumber varchar2(100),
Amount varchar2(100),
DRCRIndicator varchar2(100),
Currency varchar2(100),
TransactionType varchar2(100),
ItemSequenceNumber varchar2(100),
RoutingTransit varchar2(100),
Account varchar2(100),
CaptureSite varchar2(100),
Total_items number,
checksum_value varchar2(100));
I could not figure out how I can do mapping of field between xml and database table. If you could provide some idea about format of contral file that will be big help for me.... kind of stuck here 
JF
|
|
|
|
|
|
|
|
|
|
| Re: xml file with same segment name loading into database [message #613613 is a reply to message #613565] |
Thu, 08 May 2014 08:56  |
jamalfarooq
Messages: 35
Registered: August 2006
|
Member |
|
|
Quote:
1/ You posted your question at 22:57 for me and I think 23:57 for Littlefoot, so be patient and try to do it in by yourself in the meantime, we are not just waiting for your question, we also have a life.
I am totally agree with you Sir and my apologies ....
Quote:
2/ What is a "segment"? As far as I know this is not a XML notion.
I mean "Element" not "Segment"... value between <>.
for example : <field_name>DRCRIndicator</field_name>
<todo_group_name>Daily-CFT-ASCU</todo_group_name>
3/ What should be the result in your table for your XML data? Give us the mapping of each field.
Table Structure:
CREATE TABLE xml_test
(
scheduled_date DATE,
extract_period_start DATE,
extract_period_end DATE,
data_file_name VARCHAR2 (100),
data_file_name_2 VARCHAR2 (100),
ProcessingDate DATE,
ChequeSerialNumber VARCHAR2 (100),
Amount VARCHAR2 (100),
DRCRIndicator VARCHAR2 (100),
Currency VARCHAR2 (100),
TransactionType VARCHAR2 (100),
ItemSequenceNumber VARCHAR2 (100),
RoutingTransit VARCHAR2 (100),
Account VARCHAR2 (100),
CaptureSite VARCHAR2 (100)
);
Mapping:
Quote:
scheduled_date ==> <scheduled_date>20060602</scheduled_date>
extract_period_start ==> <extract_period_start>20060602</extract_period_start>
extract_period_end ==> <extract_period_end>20060602</extract_period_end>
data_file_name ==> <data_file_name> PSYM.CHCK.C9998.DATA.D070418.T135513.C0101 </data_file_name>
data_file_name_2 ==> <data_file_name_2>cccccccc</data_file_name_2>
ProcessingDate ==> <field_value>20060602</field_value>
ChequeSerialNumber ==> <field_value>112</field_value>
Amount ==> <field_value>33333333.33</field_value>
DRCRIndicator ==> <field_value>D</field_value>
Currency ==> <field_value>CAD</field_value>
TransactionType ==> <field_value>0</field_value>
ItemSequenceNumber ==> <field_value>500163174</field_value>
RoutingTransit ==> <field_value>21991001</field_value>
Account ==> <field_value>1032785</field_value>
CaptureSite ==> <field_value>TO</field_value>
4/ Why SQL*Loader is mandatory? Why not an external table?
I am ok with any one.
will appriciate your help, please see the attachment for data in table.
-
Attachment: xml_test.csv
(Size: 5.00KB, Downloaded 2295 times)
|
|
|
|
|
|
|
|
| Re: xml file with same segment name loading into database [message #613634 is a reply to message #613622] |
Thu, 08 May 2014 12:04 |
jamalfarooq
Messages: 35
Registered: August 2006
|
Member |
|
|
Quote:
1/ What you posted is not a CSV file, I don't know what it is
This file is to show you how the data looks in table, some how your not able to open it...
Quote:
2/ "Amount ==> <field_value>33333333.33</field_value>"
They are several "<field_name>Amount</field_name>" which one to take?
I need to get <field_value> by mapping <field_name> with database table.
for Example.
In table I have "Amount" field, now need to map <field_name>Amount</field_name> and get <field_value>33333333.33</field_value> and save it into amount field in table.
Hope this will help you to undestand...
JF
|
|
|
|
|
|
| Re: xml file with same segment name loading into database [message #613636 is a reply to message #613634] |
Thu, 08 May 2014 12:08 |
 |
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
No, it does not help.
You have
Quote:<field>
<field_name>Amount</field_name>
<field_value>33333333.33</field_value>
</field>
and
Quote: <field_name>Amount</field_name>
<field_value>22222222.22</field_value>
</field>
So why 33333333.33 and not 22222222.22?
Quote:This file is to show you how the data looks in table, some how your not able to open it...
If we don't know which kind of file it is, it is obvious we will not test all and every possible program to open it, above all if you give us the wrong extension.
[Updated on: Thu, 08 May 2014 12:09] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: xml file with same segment name loading into database [message #613640 is a reply to message #613639] |
Thu, 08 May 2014 13:48 |
|
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Here's a starter to do it (I give just a couple of fields):
SQL> col customer format a20
SQL> col ChequeSerialNumber format a30
SQL> col amount format a20
SQL> with
2 data as (
3 select '<?xml version="1.0"?>
4 <index_file>
5 <header>
6 <version>1.0</version>
7 <customer_name>CHQ_FT_Test</customer_name>
8 <todo_group_name>Daily-CFT-ASCU</todo_group_name>
9 <extraction_name>CHQ-FT-Extract</extraction_name>
10 <translation_name>CHQ-FT-TranslateToTiff</translation_name>
11 <scheduled_date>20060602</scheduled_date>
12 <extract_period_start>20060602</extract_period_start>
13 <extract_period_end>20060602</extract_period_end>
14 <text_encoding>ascii</text_encoding>
15 <content_type>cheque</content_type>
16 <checksum_field_name>Amount</checksum_field_name>
17 <data_file_name> PSYM.CHCK.C9998.DATA.D070418.T135513.C0101 </data_file_name>
18 <data_file_name_2>cccccccc</data_file_name_2>
19 </header>
20 <body>
21 <item>
22 <field>
23 <field_name>ProcessingDate</field_name>
24 <field_value>20060602</field_value>
25 </field>
26 <field>
27 <field_name>ChequeSerialNumber</field_name>
28 <field_value>112</field_value>
29 </field>
30 <field>
31 <field_name>Amount</field_name>
32 <field_value>33333333.33</field_value>
33 </field>
34 <field>
35 <field_name>DRCRIndicator</field_name>
36 <field_value>D</field_value>
37 </field>
38 <field>
39 <field_name>Currency</field_name>
40 <field_value>CAD</field_value>
41 </field>
42 <field>
43 <field_name>TransactionType</field_name>
44 <field_value>0</field_value>
45 </field>
46 <field>
47 <field_name>ItemSequenceNumber</field_name>
48 <field_value>500163174</field_value>
49 </field>
50 <field>
51 <field_name>RoutingTransit</field_name>
52 <field_value>21991001</field_value>
53 </field>
54 <field>
55 <field_name>Account</field_name>
56 <field_value>1032785</field_value>
57 </field>
58 <field>
59 <field_name>CaptureSite</field_name>
60 <field_value>TO</field_value>
61 </field>
62 <data_offset>0</data_offset>
63 <data_length>23303</data_length>
64 </item>
65 <item>
66 <field>
67 <field_name>ProcessingDate</field_name>
68 <field_value>20060602</field_value>
69 </field>
70 <field>
71 <field_name>ChequeSerialNumber</field_name>
72 <field_value>111</field_value>
73 </field>
74 <field>
75 <field_name>Amount</field_name>
76 <field_value>22222222.22</field_value>
77 </field>
78 <field>
79 <field_name>DRCRIndicator</field_name>
80 <field_value>D</field_value>
81 </field>
82 <field>
83 <field_name>Currency</field_name>
84 <field_value>CAD</field_value>
85 </field>
86 <field>
87 <field_name>TransactionType</field_name>
88 <field_value>0</field_value>
89 </field>
90 <field>
91 <field_name>ItemSequenceNumber</field_name>
92 <field_value>500163173</field_value>
93 </field>
94 <field>
95 <field_name>RoutingTransit</field_name>
96 <field_value>21991001</field_value>
97 </field>
98 <field>
99 <field_name>Account</field_name>
100 <field_value>1032785</field_value>
101 </field>
102 <field>
103 <field_name>CaptureSite</field_name>
104 <field_value>TO</field_value>
105 </field>
106 <data_offset>23303</data_offset>
107 <data_length>23272</data_length>
108 </item>
109 </body>
110 <trailer>
111 <item_count>20</item_count>
112 <checksum>
113 <checksum_field_name>Amount</checksum_field_name>
114 <checksum_field_value>512345773.75</checksum_field_value>
115 </checksum>
116 </trailer>
117 </index_file>' val
118 from dual
119 )
120 select extractvalue(xmltype(val),'//customer_name') customer, position,
121 max(decode(field_name,'ChequeSerialNumber',field_value)) ChequeSerialNumber,
122 max(decode(field_name,'Amount',field_value)) Amount
123 from data,
124 xmltable('//item' PASSING xmltype(val) columns
125 position for ordinality,
126 item xmltype path '/') x,
127 xmltable('//field' PASSING x.item columns
128 field_name varchar2(30) path '//field_name',
129 field_value varchar2(30) path '//field_value'
130 ) y
131 group by extractvalue(xmltype(val),'//customer_name'), position
132 /
CUSTOMER POSITION CHEQUESERIALNUMBER AMOUNT
-------------------- ---------- ------------------------------ --------------------
CHQ_FT_Test 2 111 22222222.22
CHQ_FT_Test 1 112 33333333.33
|
|
|
|
| Re: xml file with same segment name loading into database [message #613642 is a reply to message #613552] |
Thu, 08 May 2014 14:43 |
 |
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
If your data file is on your server, not your client, then you should be able to select from it directly, using bfilename. You can then use xmltable and max and decode to pivot the values. In the demonstration below, I have retrieved 3 columns from the header and 3 columns from the body. I have marked each place where you would need to add more code for each column with "-- and so on ...". You should be able to detect the pattern and complete the code.
-- contents of c:\my_oracle_files\sample_xml.dat:
<?xml version="1.0"?>
<index_file>
<header>
<version>1.0</version>
<customer_name>CHQ_FT_Test</customer_name>
<todo_group_name>Daily-CFT-ASCU</todo_group_name>
<extraction_name>CHQ-FT-Extract</extraction_name>
<translation_name>CHQ-FT-TranslateToTiff</translation_name>
<scheduled_date>20060602</scheduled_date>
<extract_period_start>20060602</extract_period_start>
<extract_period_end>20060602</extract_period_end>
<text_encoding>ascii</text_encoding>
<content_type>cheque</content_type>
<checksum_field_name>Amount</checksum_field_name>
<data_file_name> PSYM.CHCK.C9998.DATA.D070418.T135513.C0101 </data_file_name>
<data_file_name_2>cccccccc</data_file_name_2>
</header>
<body>
<item>
<field>
<field_name>ProcessingDate</field_name>
<field_value>20060602</field_value>
</field>
<field>
<field_name>ChequeSerialNumber</field_name>
<field_value>112</field_value>
</field>
<field>
<field_name>Amount</field_name>
<field_value>33333333.33</field_value>
</field>
<field>
<field_name>DRCRIndicator</field_name>
<field_value>D</field_value>
</field>
<field>
<field_name>Currency</field_name>
<field_value>CAD</field_value>
</field>
<field>
<field_name>TransactionType</field_name>
<field_value>0</field_value>
</field>
<field>
<field_name>ItemSequenceNumber</field_name>
<field_value>500163174</field_value>
</field>
<field>
<field_name>RoutingTransit</field_name>
<field_value>21991001</field_value>
</field>
<field>
<field_name>Account</field_name>
<field_value>1032785</field_value>
</field>
<field>
<field_name>CaptureSite</field_name>
<field_value>TO</field_value>
</field>
<data_offset>0</data_offset>
<data_length>23303</data_length>
</item>
<item>
<field>
<field_name>ProcessingDate</field_name>
<field_value>20060602</field_value>
</field>
<field>
<field_name>ChequeSerialNumber</field_name>
<field_value>111</field_value>
</field>
<field>
<field_name>Amount</field_name>
<field_value>22222222.22</field_value>
</field>
<field>
<field_name>DRCRIndicator</field_name>
<field_value>D</field_value>
</field>
<field>
<field_name>Currency</field_name>
<field_value>CAD</field_value>
</field>
<field>
<field_name>TransactionType</field_name>
<field_value>0</field_value>
</field>
<field>
<field_name>ItemSequenceNumber</field_name>
<field_value>500163173</field_value>
</field>
<field>
<field_name>RoutingTransit</field_name>
<field_value>21991001</field_value>
</field>
<field>
<field_name>Account</field_name>
<field_value>1032785</field_value>
</field>
<field>
<field_name>CaptureSite</field_name>
<field_value>TO</field_value>
</field>
<data_offset>23303</data_offset>
<data_length>23272</data_length>
</item>
</body>
<trailer>
<item_count>20</item_count>
<checksum>
<checksum_field_name>Amount</checksum_field_name>
<checksum_field_value>512345773.75</checksum_field_value>
</checksum>
</trailer>
</index_file>
-- table to load data into:
SCOTT@orcl12c> create table xml_test
2 (version varchar2(10),
3 customer_name varchar2(50),
4 todo_group_name varchar2(50),
5 extraction_name varchar2(50),
6 translation_name varchar2(50),
7 scheduled_date date,
8 extract_period_start date,
9 extract_period_end date,
10 text_encoding varchar2(50),
11 content_type varchar2(50),
12 checksum_field_name varchar2(50),
13 data_file_name varchar2(100),
14 data_file_name_2 varchar2(100),
15 ProcessingDate date,
16 ChequeSerialNumber varchar2(100),
17 Amount varchar2(100),
18 DRCRIndicator varchar2(100),
19 Currency varchar2(100),
20 TransactionType varchar2(100),
21 ItemSequenceNumber varchar2(100),
22 RoutingTransit varchar2(100),
23 Account varchar2(100),
24 CaptureSite varchar2(100),
25 Total_items number,
26 checksum_value varchar2(100));
Table created.
-- Oracle directory object that points to the directory path the file is in:
SCOTT@orcl12c> CREATE OR REPLACE DIRECTORY my_dir AS 'c:\my_oracle_files'
2 /
Directory created.
-- insert statement:
SCOTT@orcl12c> INSERT INTO xml_test
2 ( version, customer_name, todo_group_name
3 -- and so on for the rest of the column names from the header
4 , processingdate, ChequeSerialNumber, amount
5 -- and so on for the rest of the column names from the body
6 )
7 SELECT t1.version
8 , t1.customer_name
9 , t1.todo_group_name
10 -- and so on for the rest of the column names from the header
11 , MAX (DECODE (t3.field_name, 'ProcessingDate', TO_DATE (t3.field_value, 'YYYYMMDD')))
12 , MAX (DECODE (t3.field_name, 'ChequeSerialNumber', t3.field_value))
13 , MAX (DECODE (t3.field_name, 'Amount', t3.field_value))
14 -- and so on for the rest of the column names from the body
15 FROM XMLTABLE
16 ('index_file'
17 PASSING
18 XMLTYPE
19 (BFILENAME ('MY_DIR', 'sample_xml.dat'),
20 NLS_CHARSET_ID ('AL32UTF8'))
21 COLUMNS
22 version NUMBER PATH '//header/version'
23 , customer_name VARCHAR2(13) PATH '//header/customer_name'
24 , todo_group_name VARCHAR2(15) PATH '//header/todo_group_name'
25 -- and so on for the rest of the column names from the header
26 , item XMLTYPE PATH '//body/item'
27 ) t1,
28 XMLTABLE
29 ('item'
30 PASSING t1.item
31 COLUMNS
32 load_order FOR ORDINALITY,
33 field XMLTYPE PATH '//field') t2,
34 XMLTABLE
35 ('field'
36 PASSING t2.field
37 COLUMNS
38 field_name VARCHAR2(18) PATH '//field_name',
39 field_value VARCHAR2(11) PATH '//field_value'
40 ) t3
41 GROUP BY t2.load_order
42 , t1.version
43 , t1.customer_name
44 , t1.todo_group_name
45 -- and so on for the rest of the column names from the header
46 /
2 rows created.
-- results:
SCOTT@orcl12c> COLUMN version FORMAT A8
SCOTT@orcl12c> COLUMN customer_name FORMAT A13
SCOTT@orcl12c> COLUMN todo_group_name FORMAT A15
SCOTT@orcl12c> COLUMN chequeserialnumber FORMAT A19
SCOTT@orcl12c> COLUMN amount FORMAT A12
SCOTT@orcl12c> SELECT version, customer_name, todo_group_name,
2 processingdate, chequeserialnumber, amount
3 FROM xml_test
4 /
VERSION CUSTOMER_NAME TODO_GROUP_NAME PROCESSINGDATE CHEQUESERIALNUMBER AMOUNT
-------- ------------- --------------- --------------- ------------------- ------------
1 CHQ_FT_Test Daily-CFT-ASCU Fri 02-Jun-2006 112 33333333.33
1 CHQ_FT_Test Daily-CFT-ASCU Fri 02-Jun-2006 111 22222222.22
2 rows selected.
|
|
|
|
|
|
Goto Forum:
Current Time: Mon Dec 23 02:40:32 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
