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 Go to next message
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 Embarassed

JF
Re: xml file with same segment name loading into database [message #613554 is a reply to message #613552] Wed, 07 May 2014 16:46 Go to previous messageGo to next message
jamalfarooq
Messages: 35
Registered: August 2006
Member
Any advise ... Littlefoot Michael Smile
Re: xml file with same segment name loading into database [message #613555 is a reply to message #613554] Wed, 07 May 2014 16:47 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/?SQ=dd62fc24a5e4a3a772937ce0662025c4&t=search&srch=xml&btn_submit=Search&field=all&fo rum_limiter=10&attach=0&search_logic=AND&sort_order=DESC&author=
Re: xml file with same segment name loading into database [message #613565 is a reply to message #613554] Thu, 08 May 2014 01:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

jamalfarooq wrote on Wed, 07 May 2014 23:46
Any advise ... Littlefoot Michael Smile


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.

2/ What is a "segment"? As far as I know this is not a XML notion.

3/ What should be the result in your table for your XML data? Give us the mapping of each field.

4/ Why SQL*Loader is mandatory? Why not an external table?





Re: xml file with same segment name loading into database [message #613613 is a reply to message #613565] Thu, 08 May 2014 08:56 Go to previous messageGo to next message
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 1666 times)
Re: xml file with same segment name loading into database [message #613621 is a reply to message #613613] Thu, 08 May 2014 09:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

1/ What you posted is not a CSV file, I don't know what it is
2/ "Amount ==> <field_value>33333333.33</field_value>"
They are several "<field_name>Amount</field_name>" which one to take?

Re: xml file with same segment name loading into database [message #613622 is a reply to message #613621] Thu, 08 May 2014 09:20 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
storing "numbers" (like ChequeSerialNumber, Amount, ItemSequenceNumber) in VARCHAR2 columns should be avoided


Re: xml file with same segment name loading into database [message #613634 is a reply to message #613622] Thu, 08 May 2014 12:04 Go to previous messageGo to next message
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 #613635 is a reply to message #613622] Thu, 08 May 2014 12:05 Go to previous messageGo to next message
jamalfarooq
Messages: 35
Registered: August 2006
Member
Quote:

storing "numbers" (like ChequeSerialNumber, Amount, ItemSequenceNumber) in VARCHAR2 columns should be avoided


Thanks for your suggestion, will do that....
Re: xml file with same segment name loading into database [message #613636 is a reply to message #613634] Thu, 08 May 2014 12:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
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 #613637 is a reply to message #613636] Thu, 08 May 2014 12:30 Go to previous messageGo to next message
jamalfarooq
Messages: 35
Registered: August 2006
Member
Quote:

So why 33333333.33 and not 22222222.22?


Sorry for confusion, I need both values to be inserted into table, please see below the attachment that will help you....
  • Attachment: xml_test.JPG
    (Size: 80.16KB, Downloaded 1229 times)

[Updated on: Thu, 08 May 2014 12:47] by Moderator

Report message to a moderator

Re: xml file with same segment name loading into database [message #613638 is a reply to message #613637] Thu, 08 May 2014 12:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So you want several rows for the same (something I assume as we have not a clear specification):
    <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>

Is this one row per <item>?
In this case you should have 2 tables: a parent one for the <header> information (defining the customer if I understand) and a child one for each <item> (defining a transaction, still if I understand the data).

Re: xml file with same segment name loading into database [message #613639 is a reply to message #613638] Thu, 08 May 2014 12:58 Go to previous messageGo to next message
jamalfarooq
Messages: 35
Registered: August 2006
Member
So you want several rows for the same (something I assume as we have not a clear specification):

YOU GOT IT !!!

Is this one row per <item>?

YES....

Great !! that what exactly I am looking ... if you could give me some direction .. that will be very help full ...
Re: xml file with same segment name loading into database [message #613640 is a reply to message #613639] Thu, 08 May 2014 13:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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.


Re: xml file with same segment name loading into database [message #613643 is a reply to message #613640] Thu, 08 May 2014 15:36 Go to previous message
jamalfarooq
Messages: 35
Registered: August 2006
Member
great stuff ...
thanks...
will try...
Previous Topic: problem Import
Next Topic: Bug in Loading XmlType column with VARCHARC
Goto Forum:
  


Current Time: Tue Mar 19 00:11:14 CDT 2024