READING XML Master / Detail data using ORACLE PL/SQL      Sept 5th, 5th of Ramadan , 2008

The need for us at REALSOFT arises from our BI project for The  ministry of Higher Education in Oman.

All colleges and universities are supposed to submit data into the data warehouse on regular basis.

an XML transfer protocol has been agreed upon.  Our responsibility is to first design the XML file protocol then as the participants to upload (via a web screen) these files, then read the XML into Oracle database tables.



Assume that the file is called  college.xml

The master record contains the Basic college information

and the details is the student name within this college

Note that the details are nested within the Master row and indicated by the tag <row>

as can be seen in the file below


<?xml version="1.0" encoding="UTF-8"?>
    <master>                                                            <-------- first master row
        <row>                                                                <---- first row starts
        </row>                                                              <--- First row ends
        <row>                                                                <--- Second row starts
        </row>                                                               <----  second row ends                    

        <row>                                                                 <-----  nth row et....
    </master>                                                          <------ first master ends, this example has one master


The following is a PL/SQL that has been tested

It requires that you first create an ORACLE DIRECTORY called  XMLDIR

It also requires that you grant access to this DIRECTORY who shall execute the PLSQL block

It goes without saying that you should place your xml that is be read in the same DIRECTORY.

I called the file college.xml and as such you may need to change the code of you use a different name


The basic syntax to create ORACLE Directory is

SQL> CREATE DIRECTORY XMLDIR is 'PATH';   --- instead of 'PATH' you need to substitute the path you want to use


then grant access to this DIRECTORY




The following PLSQL code should read the XML file and print the information out to your screen.

I have added enough comments that shall make the idea simple and therefore, extendible



l_bfile BFILE;

l_clob CLOB;

l_parser dbms_xmlparser.Parser;

my_doc dbms_xmldom.DOMDocument-- this vairable shall hold XML parsed data

current_item_list dbms_xmldom.DOMNodeList;

current_item dbms_xmldom.DOMNode;


-- Declare local variable the correspond to the XML data

-- in more serious implementation , consider using RECORD type


master_id number;

master_name varchar2(100);

student_id number;

student_name varchar2(100);



-- Point to the XML file called college.xml which is located in the path pointed at by XMLDIR

l_bfile := BFileName('XMLDIR', 'college.xml');

dbms_lob.createtemporary(l_clob, cache=>FALSE);


-- Open the XML file in read mode, dbms_lob.lob_readonly);


-- tranfer the file contents to CLOB variable

dbms_lob.loadFromFile(dest_lob => l_clob,

src_lob => l_bfile,

amount => dbms_lob.getLength(l_bfile));


-- This ends file manipulation and the file contents are  now in a CLOB vairable


-- This entry is important to deal with DATE format in the XML data



-- Starting XML processing business

-- Create a parser.

l_parser := dbms_xmlparser.newParser;

-- Parse the document and create a new DOM document.

dbms_xmlparser.parseClob(l_parser, l_clob);


-- The xml parsed CLOB is not transfered to a variable called my_doc


my_doc := dbms_xmlparser.getDocument(l_parser);

-- Free resources associated with the CLOB and Parser now they are no longer needed.




-- The following statement shall search for a node starting with /college/master

-- Then it shall get the no of entries in this node. In our example the college/master contains only one

-- entry, but a big one, because it contains detail XML (there is ony one <master> </master>


current_item_list := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(my_doc),'college/master');

-- note no slash at end

--dbms_output.put_line(dbms_xmldom.getLength(current_item_list)); -- find the no of entries in this node


FOR cur_ent IN 0 .. dbms_xmldom.getLength(current_item_list)-1 LOOP -- it should loop only once because the matser is one row

current_item := dbms_xmldom.item(current_item_list, cur_ent); --READ the current item from current entry

dbms_xslprocessor.valueOf(current_item,'id/text()',master_id); -- get the values from the XML Tag <ID>


DBMS_OUTPUT.Put_Line( 'master value is : '|| master_id ||'-' ||master_name);


-- now identify the detail node so that you can loop over it and read its items like before


current_item_list := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(my_doc),'/college/master/row');


-- note no slash at end



    FOR cur_ent IN 0 .. dbms_xmldom.getLength(current_item_list)-1 LOOP

        current_item := dbms_xmldom.item(current_item_list, cur_ent);



                    DBMS_OUTPUT.Put_Line( '   <row> value is : '|| student_id ||'-' ||student_name );

     end loop;

end loop;





The output would be


master value is : 10-REALSOFT

  <row> value is : 1-Ahmad

  <row> value is : 2-Samia

  <row> value is : 3-Tareq