Validating and XML File against DTD external file  in PL/SQL      Sept 20, 2008

I have spent sometime researching this subject until I finally came to an acceptable solution

In a previous tip, an example was given on how to read an XML.

In this tip, validating this XML against a DTD is explained

First of all, you need to create an XML file and give it a name, I called it test.dtd

Second, it is important to realize that DBMS_XMLParser package (as is the case with other Oracle packages) do not write and read file directly to/from the OS for security reasons.

This implies that we need to do some steps to store the test.dtd within the database. The approach here is to save it into the Oracle XML DB repository

To transfer the test.dtd to the repository we need to understand that Oracle XML DB repository allows you to transfer files into the Oracle database using FTP (!!!! YES there is an FTP server inside the Oracle database) :-

To test it run the following command

SQL>select DBMS_XDB.GETFTPPORT() from dual;

if the answer is zero, then you need to set the FTP port

SQL>call dbms_xdb.setFTPPORT(2100);  -- setting the ftp port to 2100

SQL>alter system register;

To test is go to your DOS command prompt by using windows Start--> Run -->cmd

Here we are going to start an FTP session on port 2100 and connect to SCOTT

Type ftp  

ftp>Open localhost 2100       -- assuming you are running a local Oracle instance on your machine

Connected to Ammar.
220- Ammar
Unauthorised use of this FTP server is prohibited and may be subject to civil an
d criminal prosecution.
220 Ammar FTP Server (Oracle XML DB/Oracle Database) ready.
User (Ammar:(none)):SCOTT

331 pass required for SCOTT
Password:

230 SCOTT logged in

Then check the contents by typing dir

ftp> dir
200 PORT Command successful
150 ASCII Data Connection
drw-r--r-- 2 SYS oracle 0 MAY 18 07:21 home
drw-r--r-- 2 SYS oracle 0 SEP 20 20:19 public
drw-r--r-- 2 SYS oracle 0 MAY 18 07:21 sys
-rw-r--r-- 1 SYS oracle 0 SEP 20 11:11 xdbconfig.xml
226 ASCII Transfer Complete
ftp: 250 bytes received in 0.00Seconds 250000.00Kbytes/sec.
 

now you can check what is inside the public directory by using the cd command and then dir

ftp>cd public

ftp>dir

Therefore you can use the put command to transfer the dtd file

however, there is another approach as well

SQL>declare
res boolean;
begin
       res := dbms_xdb.createResource('/public/test.dtd',bfilename('XMLDIR','test.dtd'));
end;
/
commit;

XMLDIR is an Oracle directory pointing to where the dtd file is located and /public/ is the folder within XML DB repository where the DTD is going to reside

Next, you need to edit your XML and associate the XML file with DTD file using the highlighted  line

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE college SYSTEM "/public/test.dtd">    -- this line informs the parser to validate againt the specified DTD
<college>
    <master>
        <id>10</id>
        <name>ammar</name>
        <row>
            <student_id>1</student_id>
            <student_name>Ahmad</student_name>
        </row>
        <row>
            <student_id>2</student_id>
            <student_name>Samia</student_name>
        </row>
    </master>
</college>

Note:  The DTD is given at the end of the page

Run the example given in the Loading XML file tip without changes, this time the PL/SQL parser shall also validate against the DTD.

To summaries:

1) Create you DTD file

2) store the DTD in the XML DB repository

3) modify the XML file and add  !DOCTYPE college SYSTEM "/public/test.dtd

<?xml encoding="UTF-8"?>
<!ELEMENT college (master)>
<!ATTLIST college
xmlns CDATA #FIXED ''>
<!ELEMENT master (id,name,row+)>
<!ATTLIST master
xmlns CDATA #FIXED ''>
<!ELEMENT id (#PCDATA)>
<!ATTLIST id
xmlns CDATA #FIXED ''>
<!ELEMENT name (#PCDATA)>
<!ATTLIST name
xmlns CDATA #FIXED ''>
<!ELEMENT row (student_id,student_name)>
<!ATTLIST row
xmlns CDATA #FIXED ''>
<!ELEMENT student_id (#PCDATA)>
<!ATTLIST student_id
xmlns CDATA #FIXED ''>
<!ELEMENT student_name (#PCDATA)>
<!ATTLIST student_name
xmlns CDATA #FIXED ''>