XML Schema Validate using XMLType Columns

From: Kenny <tom.smith720_at_ntlworld.com>
Date: Wed, 11 Feb 2004 22:27:49 +0000
Message-ID: <yZxWb.10330$q%6.3298872@newsfep2-win.server.ntli.net>

I am using 9.2.0.4.0 version of Oracle

I would like to use the XMLTYPE based on a XML Schema to validate my xml

documents.

Given the attached XSD schema definition (sample.xsd) and using the generated XML file(sample.xml), we ran a number of tests to exercise the schema validation provided within Oracle XDB. Our tests, using the SQL file (sample.sql), showed that while XML-schema validation features were functional/correct, including:

1). XML base validation - element and attribute well formedness
2). Enumeration validation
3). Date format validation

there were some features that either functioned partially, or were absent, such as:

1). Optional/Mandatory discriminators using minOccurs and maxOccurs An element defined without minOccurs qualifier can be omitted without errors 2). Frequency discriminators using minOccurs An element with minOccurs="2" passes with one or zero instances of the element even though an attempt to insert 5 instances against a maxOccurs="3" qualifier fails
3). Range discriminators using minInclusive and maxInclusive on xsd:nonNegativeInteger type element An element with a negative integer can be inserted without errors, also an integer greater than that specified in the maxInclusive qualifier goes through without errors.

I was wondering if any one has experienced the same problems.

--_at_C:\Lanre\SunGard\NPDDEV\sample.sql

SET SERVEROUTPUT ON SIZE 800000 EXEC dbms_xmlschema.deleteSchema('http://&&dbUName:&&dbUPort/home/&&USERNAME/sample.xsd', dbms_xmlschema.DELETE_CASCADE_FORCE); EXEC dbms_xmlschema.registerSchema('http://&&dbUName:&&dbUPort/home/&&USERNAME/sample.xsd', xdbUriType('/home/&&USERNAME/sample.xsd').getClob(), TRUE, TRUE, FALSE, TRUE);

DROP TABLE sample_table;
CREATE TABLE sample_table(sample_id number(10), sample_update sys.XMLType)

	xmltype column sample_update
	XMLSCHEMA  "http://&&dbUName:&&dbUPort/home/&&USERNAME/sample.xsd" element "SimpleMessage";

DELETE FROM sample_table WHERE sample_id >= 2147484647;

DECLARE         v_xml_sample VARCHAR2(4000) := '
<SimpleMessage xmlns="http://npddev:8080/home/LANRE/sample.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://npddev:8080/home/LANRE/sample.xsd http://npddev:8080/home/LANRE/sample.xsd">

<MsgID>abc</MsgID>
<MsgHeader>String1</MsgHeader>
<MsgHeader>String2</MsgHeader>
<MsgHeader>String3</MsgHeader>
<!--
<MsgHeader>String4</MsgHeader>
<MsgHeader>String5</MsgHeader>

	-->


	<MsgBody>

		<Preamble>String</Preamble>
		<Message>String</Message>


	</MsgBody>

	<MsgFooter>

		<MsgFooterID>Page Footer</MsgFooterID>
		<Hide>Y</Hide>
		<PageNumber>24587</PageNumber>
		<CurrentDate>2004-02-09T18:01:08</CurrentDate>
		<Footer>Strings</Footer>


	</MsgFooter>

</SimpleMessage>
';

BEGIN

	INSERT INTO sample_table VALUES (2147484647, XMLTYPE(v_xml_sample));
	COMMIT;

EXCEPTION
WHEN OTHERS THEN
        dbms_output.put_line('sample_table:Exception(' || sqlcode || ') => ' || substr(sqlerrm, 0, 200)); END;
/

SHOW ERRORS; DECLARE         v_xml_sample VARCHAR2(4000) := '
<SimpleMessage xmlns="http://npddev:8080/home/LANRE/sample.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://npddev:8080/home/LANRE/sample.xsd http://npddev:8080/home/LANRE/sample.xsd">

<!--
<MsgID>bcd</MsgID>
<MsgHeader>String1</MsgHeader>
<MsgHeader>String2</MsgHeader>
<MsgHeader>String3</MsgHeader>
<MsgHeader>String4</MsgHeader>
<MsgHeader>String5</MsgHeader>
<MsgBody>

		<Preamble>String</Preamble>
		<Message>String</Message>


	</MsgBody>

	-->


	<MsgFooter>

		<MsgFooterID>Page Footer</MsgFooterID>
		<Hide>Y</Hide>
		<PageNumber>-1807</PageNumber>
		<CurrentDate>2004-02-09T18:01:08</CurrentDate>
		<Footer>String</Footer>


	</MsgFooter>

</SimpleMessage>
';

BEGIN

	INSERT INTO sample_table VALUES (2147484648, XMLTYPE.CREATEXML(v_xml_sample, 'http://npddev:8080/home/LANRE/sample.xsd', 1, 1).createSchemaBasedXML('http://npddev:8080/home/LANRE/sample.xsd'));
	--XMLTYPE.schemaValidate();
	--XMLTYPE.schemaValidate(v_xml_sample, 'http');
	--XMLTYPE.schemaValidate(XMLTYPE(v_xml_sample));
	--XMLTYPE(v_xml_sample).schemaValidate;
	--XMLTYPE.schemaValidate(XMLTYPE(v_xml_sample));
	--XMLTYPE(v_xml_sample).schemaValidate();
	--dbms_output.put_line('Schema Validation returns ... ' || XMLTYPE(v_xml_sample).schemaValidate());
	--isSchemaValid()
	--INSERT INTO sample_table VALUES (2147484649, XMLTYPE.CREATEXML(v_xml_sample).schemaValidate());
	COMMIT;

EXCEPTION
WHEN OTHERS THEN
        dbms_output.put_line('sample_table:Exception(' || sqlcode || ') => ' || substr(sqlerrm, 0, 200)); END;
/

SHOW ERRORS; COMMIT
/

SELECT * FROM sample_table WHERE sample_id >= 2147484647;

Received on Wed Feb 11 2004 - 16:27:49 CST