Oracle syntax sqlplus

Ok, I have this:


1.      In the database create the KRAKEN directory.

SQL> create directory kraken as '/files/KRAKEN';

2.      Create a table with an xmltype column

SQL> create table test_table (xml_data xmltype not null);

3.      Insert the XML document into the table with the xmltype column

SQL>insert into TEST_TABLE values (XMLTYPE(BFILENAME ('KRAKEN','load-profile-24hrs-20110111.xml'),nls_charset_id('AL32UTF8')));

4.      Create a table which contains a column for each element to be parsed

create table adam_test(
HandheldRegistration    varchar2(50),
DefaultRegistration    varchar2(50),
NonDefaultRegistration    varchar2(50),
HandheldDeactivatedReg    varchar2(50),
ActiveSuspend          varchar2(50),
ActiveDeactivate    varchar2(50),
ActiveResume    varchar2(50),
DefaultResume    varchar2(50),
ActiveActivate    varchar2(50),
SimSwap    varchar2(50),
PinSwap    varchar2(50),
ActiveModify    varchar2(50),
DeactivateToModify    varchar2(50),
DefaultModify    varchar2(50),
NonDefaultModify    varchar2(50),
WebSLActivate    varchar2(50),
CaribouDevices    varchar2(50),
CaribouLatest    varchar2(50),
REGG    varchar2(50),
REGC    varchar2(50),
REGI    varchar2(50),
REGW    varchar2(50),
CreateBBID    varchar2(50),
LookUpBBID    varchar2(50),
DeleteBBID    varchar2(50),
ChangeBBID    varchar2(50),
BBIDSBP    varchar2(50),
BISNoteUpdate    varchar2(50),
BISNoteMultiByte    varchar2(50),
ARI    varchar2(50),
BBIDLogin    varchar2(50),
WebLogin    varchar2(50),
WebSBP    varchar2(50),
WebDeviceLookUp    varchar2(50),
WebAccountLookUp    varchar2(50),
WebDevDefServsAssignment    varchar2(50),
WebServiceLookUp varchar2(50)
);

5.      Query the xmltype column and extract the elements into the table

insert into adam_test
select extractvalue(value(s), '/carrierevents/HandheldRegistration', 'xmlns="http://www.kraken.prv.com/profiler/jaxb"') HandheldRegistration,
       extractvalue(value(s), '/carrierevents/DefaultRegistration', 'xmlns="http://www.kraken.prv.com/profiler/jaxb"') DefaultRegistration,
       extractvalue(value(s), '/carrierevents/NonDefaultRegistration', 'xmlns="http://www.kraken.prv.com/profiler/jaxb"') NonDefaultRegistration,
       extractvalue(value(s), '/carrierevents/HandheldDeactivatedRegistration', 'xmlns="http://www.kraken.prv.com/profiler/jaxb"') HandheldDeactivatedReg,
       extractvalue(value(s), '/carrierevents/ActiveSuspend', 'xmlns="http://www.kraken.prv.com/profiler/jaxb"') ActiveSuspend,
       extractvalue(value(s), '/carrierevents/ActiveDeactivate', 'xmlns="http://www.kraken.prv.com/profiler/jaxb"') ActiveDeactivate,
       extractvalue(value(s), '/carrierevents/ActiveResume', 'xmlns="http://www.kraken.prv.com/profiler/jaxb"') ActiveResume,
       extractvalue(value(s), '/carrierevents/DefaultResume', 'xmlns="http://www.kraken.prv.com/profiler/jaxb"') DefaultResume,
       extractvalue(value(s), '/carrierevents/ActiveActivate', 'xmlns="http://www.kraken.prv.com/profiler/jaxb"') ActiveActivate,
       extractvalue(value(s), '/carrierevents/SimSwap', 'xmlns="http://www.kraken.prv.com/profiler/jaxb"') SimSwap,
       extractvalue(value(s), '/carrierevents/PinSwap', 'xmlns="http://www.kraken.prv.com/profiler/jaxb"') PinSwap,
       extractvalue(value(s), '/carrierevents/ActiveModify', 'xmlns="http://www.kraken.prv.com/profiler/jaxb"') ActiveModify,
       extractvalue(value(s), '/carrierevents/DeactivateToModify', 'xmlns="http://www.kraken.prv.com/profiler/jaxb"') DeactivateToModify,
       extractvalue(value(s), '/carrierevents/DefaultModify', 'xmlns="http://www.kraken.prv.com/profiler/jaxb"') DefaultModify,
       extractvalue(value(s), '/carrierevents/NonDefaultModify', 'xmlns="http://www.kraken.prv.com/profiler/jaxb"') NonDefaultModify,
       extractvalue(value(s), '/carrierevents/WebSLActivate', 'xmlns="http://www.kraken.prv.com/profiler/jaxb"') WebSLActivate,
       extractvalue(value(s), '/carrierevents/CaribouDevices', 'xmlns="http://www.kraken.prv.com/profiler/jaxb"') CaribouDevices,
       extractvalue(value(s), '/carrierevents/CaribouLatest', 'xmlns="http://www.kraken.prv.com/profiler/jaxb"') CaribouLatest,
       extractvalue(value(s), '/carrierevents/REGG', 'xmlns="http://www.kraken.prv.com/profiler/jaxb"') REGG,
       extractvalue(value(s), '/carrierevents/REGC', 'xmlns="http://www.kraken.prv.com/profiler/jaxb"') REGC,
       extractvalue(value(s), '/carrierevents/REGI', 'xmlns="http://www.kraken.prv.com/profiler/jaxb"') REGI,
       extractvalue(value(s), '/carrierevents/REGW', 'xmlns="http://www.kraken.prv.com/profiler/jaxb"') REGW,
       extractvalue(value(s), '/carrierevents/CreateBBID', 'xmlns="http://www.kraken.prv.com/profiler/jaxb"') CreateBBID,
       extractvalue(value(s), '/carrierevents/LookUpBBID', 'xmlns="http://www.kraken.prv.com/profiler/jaxb"') LookUpBBID,
       extractvalue(value(s), '/carrierevents/DeleteBBID', 'xmlns="http://www.kraken.prv.com/profiler/jaxb"') DeleteBBID,
       extractvalue(value(s), '/carrierevents/ChangeBBID', 'xmlns="http://www.kraken.prv.com/profiler/jaxb"') ChangeBBID,
       extractvalue(value(s), '/carrierevents/BBIDSBP', 'xmlns="http://www.kraken.prv.com/profiler/jaxb"') BBIDSBP,
       extractvalue(value(s), '/carrierevents/BISNoteUpdate', 'xmlns="http://www.kraken.prv.com/profiler/jaxb"') BISNoteUpdate,
       extractvalue(value(s), '/carrierevents/BISNoteMultiByte', 'xmlns="http://www.kraken.prv.com/profiler/jaxb"') BISNoteMultiByte,
       extractvalue(value(s), '/carrierevents/ARI', 'xmlns="http://www.kraken.prv.com/profiler/jaxb"') ARI,
       extractvalue(value(s), '/carrierevents/BBIDLogin', 'xmlns="http://www.kraken.prv.com/profiler/jaxb"') BBIDLogin,
       extractvalue(value(s), '/carrierevents/WebLogin', 'xmlns="http://www.kraken.prv.com/profiler/jaxb"') WebLogin,
       extractvalue(value(s), '/carrierevents/WebSBP', 'xmlns="http://www.kraken.prv.com/profiler/jaxb"') WebSBP,
       extractvalue(value(s), '/carrierevents/WebDeviceLookUp', 'xmlns="http://www.kraken.prv.com/profiler/jaxb"') WebDeviceLookUp,
       extractvalue(value(s), '/carrierevents/WebAccountLookUp', 'xmlns="http://www.kraken.prv.com/profiler/jaxb"') WebAccountLookUp,
       extractvalue(value(s), '/carrierevents/WebDevDefServsAssignment', 'xmlns="http://www.kraken.prv.com/profiler/jaxb"') WebDevDefServsAssignment,
       extractvalue(value(s), '/carrierevents/WebServiceLookUp', 'xmlns="http://www.kraken.prv.com/profiler/jaxb"') WebServiceLookUp
from table(xmlsequence(extract((select xml_data from test_table), '/kraken/profile/intervalresults/carrierevents', 'xmlns="http://www.kraken.prv.com/profiler/jaxb"'))) s;

Thanks but that doesn't show me the raw data with the attributes and what attributes you need selected.

Thank you very much.  I tried and it helped.  Thanks for a great help.