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