• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 407
  • Last Modified:

Oracle syntax sqlplus

Here is some code.  Extracting elements works fine.  But no clue about extracting attributes.

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;

                This works fine for extracting elements, but, I also need to extract attributes.    

Would you know an effective oracle syntax for extracting the attributes?   Or is it easier to convert the attributes into elements prior to inserting into oracle so I can extract with the above syntax?
0
farzanj
Asked:
farzanj
  • 2
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
>>effective oracle syntax for extracting the attributes

Just use the '@' on the extract.  If you can provide some sample data and expected results we can provide a working solution.
0
 
farzanjAuthor Commented:
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;
0
 
slightwv (䄆 Netminder) Commented:
Thanks but that doesn't show me the raw data with the attributes and what attributes you need selected.
0
 
farzanjAuthor Commented:
Thank you very much.  I tried and it helped.  Thanks for a great help.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now