2 Replies Latest reply: Aug 7, 2013 8:37 AM by 47e033bd-d313-47bd-9372-871358ce3c3e RSS

    DBMS_XMLDOM.WRITETOFILE to append sysdate with timings


      Hi i need a help in storing the xml file in a directory with date and time , please see my code below



      doc  DBMS_XMLDOM.DOMDocument;                                                                                       

      xdata  XMLTYPE;                                                                                                     

      CURSOR xmlcur IS                                                                                                    

      SELECT XMLElement(


      XMLAttributes(d.department_id AS "DepartmentId"),

      XMLForest(d.department_name AS "Name"),


      XMLForest(street_address AS "Address",

      city AS "City",

      state_province AS "State",

      postal_code AS "Zip",

      country_name AS "Country")),



      (SELECT XMLAgg(



      XMLAttributes(e.employee_id AS "employeeNumber"),

      XMLForest(e.first_name AS "FirstName",

      e.last_name AS "LastName",

      e.email AS "EmailAddress",

      e.phone_number AS "PHONE_NUMBER",

      e.hire_date AS "StartDate",

      j.job_title AS "JobTitle",

      e.salary AS "Salary",

      m.first_name || ' ' || m.last_name AS "Manager"),

      XMLElement("Commission", e.commission_pct)))

      FROM hr.employees e, hr.employees m, hr.jobs j

      WHERE e.department_id = d.department_id

      AND j.job_id = e.job_id

      AND m.employee_id = e.manager_id))).extract('/*')

      AS XML

      FROM hr.departments d, hr.countries c, hr.locations l

      WHERE department_name = 'Executive'

      AND d.location_id = l.location_id

      AND l.country_id = c.country_id;                                                                                                                       


      OPEN xmlcur;                                                                                                        

      FETCH xmlcur INTO xdata;                                                                                            

      CLOSE xmlcur;                                                                                                       

      doc := DBMS_XMLDOM.NewDOMDocument(xdata);                                                                           

      DBMS_XMLDOM.WRITETOFILE(doc,'MENDIX/mendix.xml'); ---- lines to be changed                                                                 




      In this i want to append the sysdate with time to the xml file

      DBMS_XMLDOM.WRITETOFILE(doc,'MENDIX/mendix+ sysdate.xml')

      Anybody please help on this