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

http://servername:8080

ftp://servername:2100

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...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbserver.realsoft.com)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbserver.realsoft.com)(PORT=8080))(
presentation=HTTP)(Session=RAW))

Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "orclstc.realsoft.com" has 1 instance(s).
Instance "orclstc", status READY, has 1 handler(s) for this service...
Service "orclstcXDB.realsoft.com" 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  http://192.168.0.3:8080/sys/schemas/JABR/)

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

declare

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

Begin

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

-- the root for this url is http://192.168.0.3:8080/sys/schemas/JABR/
--  also note that you can remove http://  entirely

schema_:= '<?xml version="1.0" encoding="UTF-8"?>
<schema xmlns="http://www.w3.org/2001/XMLSchema" -- 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
elementFormDefault="qualified">
<!-- Get Payment Methods   validation rules start here -->
<element name="GetPaymentMethodsRequest">
<annotation>    -- annotation does not interfere with validation, just documentation
    <documentation>
        Request message for Get Payment Methods operations.
</documentation>
</annotation>
<complexType>
    <sequence>
        <element name="lang" type="string" />
         <element name="eServiceId" maxOccurs="1" minOccurs="1">
            <simpleType>
                <restriction base="string">
                    <length value="8"></length>
                    <pattern value="\d{8}"></pattern>
                </restriction>
            </simpleType>
        </element>
    </sequence>
</complexType>
</element>
</schema>';

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

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

End;

 

The following example validates a simple XML document

Declare
    var varchar2(200);
    xml_doc XMLTYPE;
    serviceId varchar2(1000);
    valid number;
    schema_url varchar2(2000);
Begin
-- 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
<lang>En</lang>
<eServiceId>43800001</eServiceId>
</GetPaymentMethodsRequest>');

var:='the var variable'; --testing
dbms_output.put_Line(var);

-- var := SYS.XMLTYPE.extract(xml_doc,'//lang/text()').getStringVal();
-- serviceId := XMLTYPE.extract(xml_doc,'//eServiceId/text()').getStringVal();
dbms_output.put_Line(var);
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(' ');

End;

 

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

http://www.oracle-base.com/articles/9i/XMLDB9i.php