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

    47e033bd-d313-47bd-9372-871358ce3c3e

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

       

      DECLARE                                                                                                               

      doc  DBMS_XMLDOM.DOMDocument;                                                                                       

      xdata  XMLTYPE;                                                                                                     

      CURSOR xmlcur IS                                                                                                    

      SELECT XMLElement(

      "Department",

      XMLAttributes(d.department_id AS "DepartmentId"),

      XMLForest(d.department_name AS "Name"),

      XMLElement("Location",

      XMLForest(street_address AS "Address",

      city AS "City",

      state_province AS "State",

      postal_code AS "Zip",

      country_name AS "Country")),

      XMLElement(

      "EmployeeList",

      (SELECT XMLAgg(

      XMLElement(

      "Employee",

      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;                                                                                                                       

      BEGIN                                                                                                                 

      OPEN xmlcur;                                                                                                        

      FETCH xmlcur INTO xdata;                                                                                            

      CLOSE xmlcur;                                                                                                       

      doc := DBMS_XMLDOM.NewDOMDocument(xdata);                                                                           

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

      END;                                                                                                                  

      /     

       

      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