Validating XML file against  Xml Schema Document  (XSD) in Oracle PL/SQL   Aug,10, 09


XSD is an XML file that contains validation rules for an XML document. Validating an XML document against an XSD file guarantees that an XML document conforms to the intended consistency rule.

Oracle and since its introduction to the XML Database (XDB), XML storage, and manipulation became handy.

To enhance data quality, it is natural to simplify techniques needed to make sure that data stored in database or sent to other external source abide by know database or information structures.

Oracle XML repository can be accessed via Dictionary views or TCP based services like FTP, HTTP, webdav etc.  The reason why I mention this is because in this example, the XSD file is going to be loaded into the Oracle Database XML repository

You can access the XML repository as follows:-



When Oracle XML DB is installed, it configures the appropriate ports through which the repository can be accessed via the above shown URLs.  Just to give an idea, you can inspect the database listener by executing the following command


$lsnrctl status
 . . . .

Listener Parameter File /home/oracle10g/product/10.1.0/Db_1/network/admin/
Listener Log File /home/oracle10g/product/10.1.0/Db_1/network/log/
Listening Endpoints Summary...

Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "" has 1 instance(s).
Instance "orclstc", status READY, has 1 handler(s) for this service...
Service "" has 1 instance(s).
The command completed successfully

Note the highlighted section above which shows that port 8080 is configured as for http services.

You can test the connection:


note that there is a file called xdbconfig.xml 

This files contains configuration information about the ports used to connect

Double click sys/schemas/JABR (or simple

to view files stored in your active resource (note JABR is the login username)

But how did these schemas got into there?

The processes of upload the xsd file into the Oracle XML DB repositry is called registration

The following example demonstrates how to register and xsd contents


schema_url varchar2(2000);
xml_contents varchar2(2000);


schema_url:= 'http://ammar9.xsd';    

-- the root for this url is
--  also note that you can remove http://  entirely

schema_:= '<?xml version="1.0" encoding="UTF-8"?>
<schema xmlns="" -- this is a namespace used for xsd documetn and has nothing to do with the XML document validation
targetNamespace="http://realsoft/XMLSchema"  -- this namespace should be used in the xml doc to -- be validated
<!-- Get Payment Methods   validation rules start here -->
<element name="GetPaymentMethodsRequest">
<annotation>    -- annotation does not interfere with validation, just documentation
        Request message for Get Payment Methods operations.
        <element name="lang" type="string" />
         <element name="eServiceId" maxOccurs="1" minOccurs="1">
                <restriction base="string">
                    <length value="8"></length>
                    <pattern value="\d{8}"></pattern>

-- dbms_XMLSchema.deleteSchema(schema_url,DBMS_XMLSchema.Delete_Cascade_Force);

This register the contents (XML_CONTENTS) to the schema url  (defined above as ammar9.xsd)
-- the xml_contents can be loaded from files etc ..   check for other example in the net



The following example validates a simple XML document

    var varchar2(200);
    xml_doc XMLTYPE;
    serviceId varchar2(1000);
    valid number;
    schema_url varchar2(2000);
-- sys/schemas/SCOTT/egov.jopay/
schema_url:= 'http://ammar5.xsd'; -- with or without http

--  the following is the XML document to be valiated
xml_doc:= XMLTYPE('<?xml version="1.0" encoding="UTF-8"?>
<GetPaymentMethodsRequest xmlns="http://realsoft/XMLSchema" >  -- note namespace is same

--  as target namespace used in the xsd document

var:='the var variable'; --testing

-- var := SYS.XMLTYPE.extract(xml_doc,'//lang/text()').getStringVal();
-- serviceId := XMLTYPE.extract(xml_doc,'//eServiceId/text()').getStringVal();
dbms_output.put_Line('Service ID is : '||serviceId);

--xml_doc.schemaValidate(); -- an error that the xml is not schema based!!
-- here the xml_doc is the content of the xml itself being used as if it is an instance of an object

--  Then a method called isSchemaValid is invoked

--valid :=XMLIsValid(xml_doc,schema_url,'GetPaymentMethodsRequest');

valid :=xml_doc.isSchemaValid(schema_url);
dbms_output.put_Line('After validation: '|| valid);
dbms_output.put_Line(' ');



In order to find the registered schemas use


select local, schema_url, QUAL_SCHEMA_URL from dba_xml_Schemas;


a general review of XML DB is found at