Skip to Main Content
  • Questions
  • HOW TO USE isSchemaValid() in oracle 11gr2 to validate XML against XSD schema?

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, ERKAN.

Asked: May 31, 2016 - 7:43 am UTC

Last updated: May 31, 2016 - 8:52 am UTC

Version: 11gr2

Viewed 1000+ times

You Asked

I have an XSD schema on my hard disk .. I want it to upload in to the database and then using this schema I want to validate xml files created on the fly via the Oracle 11gr2 builtin functions ?

Would you provide a step by step procedure to do so ?

Thanks in advance ...

and we said...

To validate XML against an XSD, first you need to register the XSD.

For this example, I'm using the following XML:

<order>
  <orderId>1</orderId>
  <customerId>123</customerId>
</order>


And checking it conforms to the following XSD:

<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="order">
    <xs:complexType>
      <xs:sequence>
        <xs:element type="xs:byte" name="orderId"/>
        <xs:element type="xs:byte" name="customerId"/>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>


(generated using http://www.freeformatter.com/xsd-generator.html#ad-output )

You register it by calling DBMS_XMLschema.registerSchema:

begin
  dbms_xmlschema.registerSchema('order.xsd',
'<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="order">
    <xs:complexType>
      <xs:sequence>
        <xs:element type="xs:byte" name="orderId"/>
        <xs:element type="xs:byte" name="customerId"/>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>');
end;
/


You can then check whether XMLType instances conform using isSchemaValid.

This does conform, so returns 1:

declare
  xml xmltype;
begin
  xml := xmltype('<order>
  <orderId>1</orderId>
  <customerId>123</customerId>
</order>');
  
  dbms_output.put_line(xml.isSchemaValid('order.xsd'));
end;
/
1


This is missing customerId, so doesn't. It returns 0:

declare
  xml xmltype;
begin
  xml := xmltype('<order>
  <orderId>1</orderId>
  <customerId>123</customerId>
</order>');
  
  dbms_output.put_line(xml.isSchemaValid('order.xsd'));
end;
/
0


This doesn't give much information! You can get further details about why a document doesn't conform by calling XMLType.schemaValidate. To use this, your XML must have the correct namespace info.

To do this, add:

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="order.xsd"


Do this and you now get an exception if the XML doesn't conform:

declare
  xml xmltype;
begin
  xml := xmltype('<order xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="order.xsd">
  <orderId>1</orderId>
</order>');
  
  xmltype.schemaValidate(xml);
end;
/

ORA-31154: invalid XML document
ORA-19202: Error occurred in XML processing
LSX-00213: only 0 occurrences of particle "customerId", minimum is 1
ORA-06512: at "SYS.XMLTYPE", line 354
ORA-06512: at line 8
31154. 00000 -  "invalid XML document" 
*Cause:    The XML document is invalid with respect to its XML Schema.
*Action:   Fix the errors identified and try again.


You can find further examples at:

http://when-others-then-null.blogspot.co.uk/2013/01/validate-xml-in-oracle-against-xsd.html
https://docs.oracle.com/cd/B19306_01/appdev.102/b14259/xdb08tra.htm#i1024967

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library