Assume that the file is called

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"?>
<college>
    <master>                                                            <-------- first master row
        <id>10</id>
        <name>REALSOFT</name>
        <row>                                                                <---- first row starts
            <student_id>1</student_id>
            <student_name>Ahmad</student_name>
        </row>                                                              <--- First row ends
        <row>                                                                <--- Second row starts
            <student_id>2</student_id>
            <student_name>Samia</student_name>
        </row>                                                               <---- second row ends

        <row>                                                                 <----- nth row et....
            <student_id>3</student_id>
            <student_name>Tareq</student_name>
        </row>
    </master>                                                          <------ first master ends, this example has one master
</college>

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

SQL>GRANT READ ON DIRECTORY XMLDIR to SCOTT:

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

declare

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);

BEGIN

-- 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.open(l_bfile, 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));

dbms_lob.close(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

dbms_session.set_nls('NLS_DATE_FORMAT','''DD-MON-YYYY''');

-- 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.

dbms_lob.freetemporary(l_clob);

dbms_xmlparser.freeParser(l_parser);

-- 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_xslprocessor.valueOf(current_item,'name/text()',master_name);

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

--dbms_output.put_line(dbms_xmldom.getLength(current_item_list));

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

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

dbms_xslprocessor.valueOf(current_item,'student_id/text()',student_id);

dbms_xslprocessor.valueOf(current_item,'student_name/text()',student_name);

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

end loop;

dbms_xmldom.freeDocument(My_doc);

end;

The output would be

master value is : 10-REALSOFT

<row> value is : 1-Ahmad

<row> value is : 2-Samia

<row> value is : 3-Tareq