1 Reply Latest reply on Jan 14, 2016 5:16 PM by rp0428

    Merge and XMLTYPE

    user13324051

      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

        • 1. Re: Merge and XMLTYPE

          Can you explain me what I'm doing wrong ?

          Sure - three things -

           

          1. Your question has NOTHING to do with Sql Developer - so you have posted in the wrong forum

          2. You haven't said what problem, if any, you are even having

          3. You haven't posted ANY exception message or other error message indicating a problem even exists

           

          Please mark the thread ANSWERED and repost it in the SQL and PL/SQL forum

          SQL & PL/SQL

           

          When you repost you need to describe, in English, what PROBLEM you are having.

           

          And you need to SHOW US, not just tell us

           

          1. WHAT you do

          2. HOW you do it

          3. WHAT results you get

          4. WHAT results you expected to get

           

          Post an EXACT copy of the sql*plus session showing what code you execute and the actual results.