Follow Us on Twitter

dbms_xmldom: write a xml document to a file

by Jan Thuis on June 1, 2010 · 7 comments

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.

dbms_xmldom: write a xml document to a file , 3.7 out of 5 based on 3 ratings
Ratings:
VN:F [1.9.22_1171]
Rating: 3.7/5 (3 votes cast)

{ 7 comments… read them below or add one }

Ian Hoogeboom June 1, 2010 at 5:27 pm

Hi Jan,

If you need a DBA for granting the create rights, it’s better to let the DBA create the directory and grant read, write to the user hr:

create or replace directory myxmldata as ‘/tmp’;
grant read, write on directory myxmldata to hr;

Otherwise the hr user is able to create directories everywhere on the system where the oracle OS as access to, which could eventually damage database files…

Cheers,
Ian.

Reply

Thom June 2, 2010 at 9:25 am

Hallo Jan,

Leuk artikel!

De link in “(more information about xmltype view’s here)” wijst naar een page die niet bestaat (ik krijg een 404).

Vriendelijke groet,

Thom

Reply

Frank Dorst June 2, 2010 at 9:40 am

Beste Thom,
Bedankt voor je feedback. Ik heb de link aangepast (kwestie van case sensitivity).
Gr. Frank

Reply

Anar July 4, 2012 at 12:35 pm

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 xmldata;
close xmlcur;
doc := dbms_xmldom.newdomdocument(xmldata);
dbms_xmldom.writetofile(doc, ‘MYXMLDATA/departments.xml’);
end;

Reply

Paul Handley February 3, 2015 at 12:56 pm

Hello Jan,

I’m new to XML and have been asked to produce an XML file from a table. I’ve viewed various sites and it appears the recommendation is to use “dbms_xmldom”.

Using your example, I am nearly there but I can’t figure out how to prevent element from repeating or the place the elemnt in the output (pease see code and output below)

Any assistance is greatly appreciated.
Kind regards,
Paul

–The DTD for this file is:

— Required XML Example

SITE1

T3032ARN
2015-01-18 14:26:45

T3032ARN
2015-01-18 14:26:59

SITE2

WYS123
2015-01-18 14:26:45

XYZ321
2015-01-18 14:26:59

— MY PLSQL
DECLARE
l_xmltype XMLTYPE;

l_domdoc dbms_xmldom.DOMDocument;

l_root_node dbms_xmldom.DOMNode;

l_rec_at_loc_element dbms_xmldom.DOMElement;
l_rec_at_loc_node dbms_xmldom.DOMNode;

l_rec_element dbms_xmldom.DOMElement;
l_rec_node dbms_xmldom.DOMNode;

l_loc_element dbms_xmldom.DOMElement;
l_loc_node dbms_xmldom.DOMNode;
l_loc_text dbms_xmldom.DOMText;
l_loc_textnode dbms_xmldom.DOMNode;

l_workstation_element dbms_xmldom.DOMElement;
l_workstation_node dbms_xmldom.DOMNode;
l_workstation_text dbms_xmldom.DOMText;
l_workstation_textnode dbms_xmldom.DOMNode;

l_amu_timestamp_element dbms_xmldom.DOMElement;
l_amu_timestamp_node dbms_xmldom.DOMNode;
l_amu_timestamp_text dbms_xmldom.DOMText;
l_amu_timestamp_textnode dbms_xmldom.DOMNode;
BEGIN
— Create an empty XML document
l_domdoc := dbms_xmldom.newDomDocument;

— Create a root node
l_root_node := dbms_xmldom.makeNode(l_domdoc);

— Create a new node Rec_at_loc and add it to the root node
l_rec_at_loc_element := dbms_xmldom.createElement(l_domdoc, ‘received_receptacle_at_loc’ );
l_rec_at_loc_node := dbms_xmldom.appendChild(l_root_node,dbms_xmldom.makeNode(l_rec_at_loc_element));

FOR r_rec IN (SELECT impc_location_code
, receptacle_scan
, workstation
, to_char(amu_timestamp,’YYYY-MM-DD HH24:MI:SS’) amu_timestamp
FROM my_table
)
LOOP


l_loc_element := dbms_xmldom.createElement(l_domdoc, ‘impc_location_code ‘ );
l_loc_node := dbms_xmldom.appendChild(l_rec_at_loc_node,dbms_xmldom.makeNode(l_loc_element));
l_loc_text := dbms_xmldom.createTextNode(l_domdoc, r_rec.impc_location_code );
l_loc_textnode := dbms_xmldom.appendChild(l_loc_node,dbms_xmldom.makeNode(l_loc_text));


l_rec_element := dbms_xmldom.createElement(l_domdoc, ‘receptacle_scan ‘ );
dbms_xmldom.setAttribute(l_rec_element, ‘cd’, r_rec.receptacle_scan);
l_rec_node := dbms_xmldom.appendChild(l_rec_at_loc_node,dbms_xmldom.makeNode(l_rec_element));


l_workstation_element := dbms_xmldom.createElement(l_domdoc, ‘workstation-id’ );
l_workstation_node := dbms_xmldom.appendChild(l_rec_node,dbms_xmldom.makeNode(l_workstation_element));
l_workstation_text := dbms_xmldom.createTextNode(l_domdoc, r_rec.workstation );
l_workstation_textnode := dbms_xmldom.appendChild(l_workstation_node,dbms_xmldom.makeNode(l_workstation_text));


l_amu_timestamp_element := dbms_xmldom.createElement(l_domdoc, ‘amu_timestamp’ );
l_amu_timestamp_node := dbms_xmldom.appendChild(l_rec_node,dbms_xmldom.makeNode(l_amu_timestamp_element));
l_amu_timestamp_text := dbms_xmldom.createTextNode(l_domdoc, r_rec.amu_timestamp);
l_amu_timestamp_textnode := dbms_xmldom.appendChild(l_amu_timestamp_node,dbms_xmldom.makeNode(l_amu_timestamp_text));
END LOOP;

l_xmltype := dbms_xmldom.getXmlType(l_domdoc);
dbms_xmldom.freeDocument(l_domdoc);

dbms_output.put_line(l_xmltype.getClobVal);
END;
/
— My Result

IEDUBA

006300168544219217
2013-01-10 18:37:43

IEDUBA

976400194727994800
2013-11-07 14:08:18

IEDUBA

976400194727994800
2013-11-07 14:08:41

IEDUBA

976400194727994800
2013-11-07 14:09:45

Reply

Uma July 14, 2015 at 9:46 pm

dbms_xmldom.writetofile is creating readonly file. how to change privilages?

Reply

Johnny July 11, 2018 at 9:13 am

Hi Jan,
Your procedure work fine. I’ve try to use it and I would like to get a filename including one field result. For your example, I would like to generate one XML file per “employee Nr” like : departments_EmployeeNr.xml
How I can get this ?
Thanks in advance and best regards,

Reply

Leave a Comment

 

Previous post:

Next post:

About Whitehorses
Company profile
Services
Technology

Whitehorses website

Home page
Whitebooks
Jobs

Follow us
Blog post RSS
Comment RSS
Twitter