dbms_xmldom: write a xml document to a file

Create a xml-file from the database is not difficult certainly not if you use the package dbms_xmldom.

First you have to create a directory object in the database. This directory object is a reference to a file-system directory.  You must have the system privelege to create a directory, if not (as sys):

grant create any directory to hr;

Now you can create the directory object:

create or replace directory myxmldata as '/tmp';

Next step is to create a xmltype view which shows us the correct information we want as a xml-file. (more information about xmltype view’s here)

create or replace view v_departments_xml of xmltype
  with object id (substr(extractvalue(object_value, '/Department/Name'), 1, 128)) as
    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 d.location_id = l.location_id
      and l.country_id  = c.country_id;

All we have to do now is to fetch the data into a xmltype and call the procedure dbms_xmldom.writetofile.

Notice the directory_name MYXMLDATA has to be in uppercase.

declare
  doc  dbms_xmldom.domdocument;
  xmldata  xmltype;
  cursor xmlcur is
    select object_value from v_departments_xml
     where existsnode(object_value, '/Department[Name="Executive"]') = 1;
   begin
     open xmlcur;
     fetch xmlcur
       into xdata;
     close xmlcur;
     doc := dbms_xmldom.newdomdocument(xmldata);
     dbms_xmldom.writetofile(doc, 'MYXMLDATA/departments.xml');
   end;
 /

That’s it, I told you it was easy, finally.

Ratings:

VN:F [1.9.13_1145]

please wait...

Rating: 3.0/5 (2 votes cast)

• 

 Print This PDF Version