Hi,
I've already gone through few threads in forums itself, but for my requirement nothing comes closer, I am posting my query. I've one xml file like this
<?xml version="1.0"?>
<ACCOUNT_HEADER_ACK>
<HEADER>
<STATUS_CODE>100</STATUS_CODE>
<STATUS_REMARKS>check</STATUS_REMARKS>
</HEADER>
<DETAILS>
<DETAIL>
<SEGMENT_NUMBER>2</SEGMENT_NUMBER>
<REMARKS>rp polytechnic</REMARKS>
</DETAIL>
<DETAIL>
<SEGMENT_NUMBER>3</SEGMENT_NUMBER>
<REMARKS>rp polytechnic administration</REMARKS>
</DETAIL>
<DETAIL>
<SEGMENT_NUMBER>4</SEGMENT_NUMBER>
<REMARKS>rp polytechnic finance</REMARKS>
</DETAIL>
<DETAIL>
<SEGMENT_NUMBER>5</SEGMENT_NUMBER>
<REMARKS>rp polytechnic logistics</REMARKS>
</DETAIL>
</DETAILS>
<HEADER>
<STATUS_CODE>500</STATUS_CODE>
<STATUS_REMARKS>process exception</STATUS_REMARKS>
</HEADER>
<DETAILS>
<DETAIL>
<SEGMENT_NUMBER>20</SEGMENT_NUMBER>
<REMARKS> base polytechnic</REMARKS>
</DETAIL>
<DETAIL>
<SEGMENT_NUMBER>30</SEGMENT_NUMBER>
</DETAIL>
<DETAIL>
<SEGMENT_NUMBER>40</SEGMENT_NUMBER>
<REMARKS> base polytechnic finance</REMARKS>
</DETAIL>
<DETAIL>
<SEGMENT_NUMBER>50</SEGMENT_NUMBER>
<REMARKS> base polytechnic logistics</REMARKS>
</DETAIL>
</DETAILS>
</ACCOUNT_HEADER_ACK>
Here the xml structure is like Master and child structure I want to insert that data into Oracle tables by using the sql*loader initally I tried to created one control file but in the control file I don't know how to terminate, so I created two control files
load data
infile 'acct.xml' "str '</DETAIL>'"
truncate
into table xxrp_acct_detail
TRAILING NULLCOLS
(
dummy filler terminated by "<DETAIL>",
SEGMENT_NUMBER enclosed by "<SEGMENT_NUMBER>" and "</SEGMENT_NUMBER>",
REMARKS enclosed by "<REMARKS>" and "</REMARKS>"
)
load data
infile acct.xml' "str '</HEADER>'"
truncate
into table xxrp_acct_header
fields terminated by "<HEADER>"
TRAILING NULLCOLS
(
dummy filler terminated by "<HEADER>",
STATUS_CODE enclosed by "<STATUS_CODE>" and "</STATUS_CODE>",
STATUS_REMARKS enclosed by "<STATUS_REMARKS>" and "</STATUS_REMARKS>"
)
I am referring the same xml file in both the control files, where as for the for the first control file I was able to load the records but the second which I assume as header table not able to load the rest records. I am getting the below mentioned error.
Record 2: Rejected - Error on table XXRP_ACCT_HEADER, column DUMMY.
Field in data file exceeds maximum length
Record 3: Rejected - Error on table XXRP_ACCT_HEADER, column DUMMY.
Field in data file exceeds maximum length
Actually if its possible to seggrate in one control file then it will be helpful for me. I am also open for the external table option also. Please help me in this regard.
Thanks in advance.
Regards
Nagendra