Skip to Main Content
Forums

SQL Developer

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Merge and XMLTYPE

user13324051Jan 14 2016 — edited Jan 14 2016

Hello,

I try to merge a XML file in a table

Here is the query : 

DECLARE


 v_xml XMLTYPE;


 BEGIN




v_XML := XMLTYPE(bfilename('FICHIER_IN_FB', 'events.xml'), nls_charset_id('AL32UTF8'));




MERGE INTO EVT_EVENTS t


USING 


(


  SELECT x.*


  FROM XMLTABLE


  ( 


   'events'


  passing v_xml


   columns EV_ID VARCHAR2(32) PATH '@id',  


  EV_TITLE VARCHAR(30) PATH 'title',


  EV_SUBTITLE VARCHAR2(30) PATH 'subTitle',


  EV_SHORT_DESCRIPTION VARCHAR2(1000) PATH 'shortDescription',


  EV_IS_WITHOUT_SESSION varchar2(30) PATH 'isWithoutSession',


  EV_IS_TICKET_MANDATORY varchar2(50) PATH 'isTicketHolderMandatory',


  EV_IS_CLIENT_INFORMATION varchar2(50) PATH 'isClientInformationMandatory',


  EV_STATUS varchar2(5) PATH 'status',


  EV_CREATION_DATE varchar2(30) PATH 'creationDate',


  EV_MODIFICATION_DATE varchar2(30) PATH 'modificationDate',


  EV_ORGANIZATION varchar2(30) PATH 'organization',


  EV_MIN_PRICE varchar2(30) PATH 'minPrice',


  EV_MAX_PRIZE varchar2(30) PATH 'maxPrice',


  EV_PLACE_ID varchar2(30) PATH 'place/@id'


  )x


) src


ON (t.EV_ID = src.EV_ID)




WHEN MATCHED THEN


  UPDATE SET 


  t.EV_TITLE = src.EV_TITLE,


  t.EV_SUBTITLE = src.EV_SUBTITLE ,


  t.EV_SHORT_DESCRIPTION = src.EV_SHORT_DESCRIPTION,


  t.EV_IS_WITHOUT_SESSION = src.EV_IS_WITHOUT_SESSION,


  t.EV_IS_TICKET_MANDATORY = src.EV_IS_TICKET_MANDATORY,


  t.EV_IS_CLIENT_INFORMATION = src.EV_IS_CLIENT_INFORMATION,


  t.EV_STATUS = src.EV_STATUS,


  t.EV_CREATION_DATE = src.EV_CREATION_DATE,


  t.EV_MODIFICATION_DATE = src.EV_MODIFICATION_DATE,


  t.EV_ORGANIZATION = src.EV_ORGANIZATION,


  t.EV_MIN_PRICE = src.EV_MIN_PRICE ,


  t.EV_MAX_PRIZE = src.EV_MAX_PRIZE,


  t.EV_PLACE_ID = src.EV_PLACE_ID,


  t.ev_current_date = sysdate




WHEN NOT MATCHED THEN


  INSERT (t.EV_TITLE,


  t.EV_SUBTITLE,


  t.EV_SHORT_DESCRIPTION,


  t.EV_IS_WITHOUT_SESSION,


  t.EV_IS_TICKET_MANDATORY,


  t.EV_IS_CLIENT_INFORMATION,


  t.EV_STATUS,


  t.EV_CREATION_DATE,


  t.EV_MODIFICATION_DATE,


  t.EV_ORGANIZATION,


  t.EV_MIN_PRICE,


  t.EV_MAX_PRIZE,


  t.EV_PLACE_ID,


  t.ev_current_date)


  VALUES (src.EV_TITLE,


  src.EV_SUBTITLE, 


  src.EV_SHORT_DESCRIPTION,


  src.EV_IS_WITHOUT_SESSION,


  src.EV_IS_TICKET_MANDATORY,


  src.EV_IS_CLIENT_INFORMATION,


  src.EV_STATUS,


  src.EV_CREATION_DATE,


  src.EV_MODIFICATION_DATE,


  src.EV_ORGANIZATION,


  src.EV_MIN_PRICE,


  src.EV_MAX_PRIZE,


  src.EV_PLACE_ID,


  sysdate


   );


end;

Can you explain me what I'm doing wrong ?

Thanks

Cheers

Comments

TexasApexDeveloper

Might want to post this to the proper forum.. This is the ORDS (Formerly APEX Listener) forum.. The APEX support forum is located here:Application Express

Thank you,

Tony Miller
LuvMuffin Software
Ruckersville, VA

TexasApexDeveloper

Also here is an example of doing an Org tree in APEX using css3 coding: http://rokitta.blogspot.com/2013/12/pure-css3-org-tree-with-apex-list.html

Thank you,

Tony Miller
LuvMuffin Software
Ruckersville, VA

Edwin T

Thanks. The second solution worked.

1 - 3
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Feb 11 2016
Added on Jan 14 2016
#sql-developer
1 comment
474 views