XML Schema Validate using XMLType Columns

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

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>

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;