Hi,
I have a requirement where I should be processing multiple xml files into a table using oracle external tables.
<DeliveryStatus>
<DeliveryID>24</DeliveryID>
<Username>ABC</Username>
<StartDelivery>2014-01-29</StartDelivery>
<EndDelivery>2014-01-29</EndDelivery>
<LastProcessed>2014-01-29</LastProcessed>
<ParameterInfo>
<Category>Fruits</Category>
<Version>1.14.26</Version>
<File>Fruits.xml</File>
</ParameterInfo>
<Status>LOADED</Status>
<Statistics>
<DeliveredRecords>10000</DeliveredRecords>
<PendingRecords>0</PendingRecords>
<ProcessedRecords>10000</ProcessedRecords>
<ErroneousRecords>0</ErroneousRecords>
</Statistics>
</DeliveryStatus>
I have created external table something like below. When I query the table it is not throwing any error but returning 0 rows. I should get all the tags ( same columns in ext table). If it is simple tags it is working fine but in this case XML has multiple sub tags inside it. It would be great if you can help how to process them.
CREATE TABLE DELIVERYSTATUS_FRUITS
(
DeliveryID VARCHAR2(50 BYTE),
Username VARCHAR2(50 BYTE),
StartDelivery VARCHAR2(50 BYTE),
EndDelivery VARCHAR2(50 BYTE),
LastProcessed VARCHAR2(50 BYTE),
Category VARCHAR2(50 BYTE),
Version VARCHAR2(50 BYTE),
File VARCHAR2(50 BYTE),
Status VARCHAR2(50 BYTE),
DeliveredRecords VARCHAR2(50 BYTE),
PendingRecords VARCHAR2(50 BYTE),
ProcessedRecords VARCHAR2(50 BYTE),
ErroneousRecords VARCHAR2(50 BYTE),
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY EXT_DATA
ACCESS PARAMETERS
( records delimited by "</DeliveryStatus>"
badfile DATA_BAD:'FRUITS_EXT_%a_%p.bad'
logfile DATA_LOG:'FRUITS_EXT.log'
fields (
filler CHAR(100) terminated by "<DeliveryStatus>",
DeliveryID CHAR(100) enclosed by "<DeliveryID>" and "</DeliveryID>" ,
Username CHAR(100) enclosed by "<Username>" and "</Username>" ,
StartDelivery CHAR(100) enclosed by "<StartDelivery>" and "</StartDelivery>" ,
EndDelivery CHAR(100) enclosed by "<EndDelivery>" and "</EndDelivery>" ,
LastProcessed CHAR(100) enclosed by "<LastProcessed>" and "</LastProcessed>" ,
Category CHAR(100) enclosed by "<Category>" and "</Category>" ,
Version CHAR(100) enclosed by "<Version>" and "</Version>",
File CHAR(50) enclosed by "<File>" and "</File>",
Status CHAR(50) enclosed by "<Status>" and "</Status>",
DeliveredRecords CHAR(50) enclosed by "<DeliveredRecords>" and "</DeliveredRecords>",
ProcessedRecords CHAR(50) enclosed by "<ProcessedRecords>" and "</ProcessedRecords>",
ErroneousRecords CHAR(50) enclosed by "<ErroneousRecords>" and "</ErroneousRecords>",
)
)
LOCATION (DATA_DIR:'DeliveryStatus_Fruits.xml')
)
REJECT LIMIT UNLIMITED;