This discussion is archived
2 Replies Latest reply: Aug 7, 2013 6:37 AM by 47e033bd-d313-47bd-9372-871358ce3c3e RSS

DBMS_XMLDOM.WRITETOFILE to append sysdate with timings

47e033bd-d313-47bd-9372-871358ce3c3e Newbie
Currently Being Moderated

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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points