Hello All,
I was trying to validate XML schema against registered XSD and encountered error ORA-19030 as below:
BEGIN
FOR R IN ( SELECT code,
viewdef,
schema\_viewdef
FROM xml\_data
)
LOOP
BEGIN
if r.viewdef.isSchemaBased() = 1 then
dbms_output.put_line('Schema based');
else
dbms_output.put_line('Non-schema based');
UPDATE XML_DATA SET schema_viewdef = r.viewdef.createSchemaBasedXML('functional_view_generator.xsd')
where
code = r.code;
commit;
end if;
END;
END LOOP;
FOR R IN ( SELECT code,
schema\_viewdef
FROM xml\_data
)
LOOP
BEGIN
r.schema_viewdef.schemaValidate();
dbms_output.put_line ('XML document with code '||r.code||' is VALID');
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line (r.code||' = INVALID => '||sqlerrm);
END;
END LOOP;
END;
I have cooked below data:
create table XML_DATA
(
code varchar2 (50),
description varchar2 (50),
viewdef xmltype,
schema_viewdef xmltype
);
insert into XML_DATA values
(
'INST_CLAIMS_FV',
'A view that shows claims',
XMLTYPE
(
'<functionalView name="INST_CLAIMS_FV" description="Functional view for Institutional Claims">
<columns>
\<column name="CLAI.CODE" columnAlias="CLAIM\_CODE" description="The code of the claim" />
\<column name="CLLI.CODE" columnAlias="CLAIM\_LINE\_CODE" description="The code of the claim line" />
\<column name="PROC.CODE" columnAlias="PROCEDURE\_CODE" description="The code of the claim line procedure" />
</columns>
<fromviews>
\<fromview name="CLAIMS\_V" alias="CLAI" />
\<fromview name="CLAIM\_LINES\_V" alias="CLLI" />
\<fromview name="PROCEDURES\_V" alias="PROC" />
\<fromview name="PROVIDERS\_V" alias="PROV" />
\<fromview name="CLAIMFORMS\_V" alias="CLFO"/>
\<fromview name="CLAIMFORMTYPES\_V" alias="CFTY"/>
</fromviews>
<joins>
\<join alias1="CLLI" col1="CLAI\_ID" condition="=" alias2="CLAI" col2="ID"/>
\<leftouterjoin alias1="PROC" col1="ID" condition="=" alias2="CLLI" col2="PROC\_ID"/>
\<join alias1="CLFO" col1="ID" condition="=" alias2="CLAI" col2="CLFO\_ID"/>
\<join alias1="CFTY" col1="ID" condition="=" alias2="CLFO" col2="CFTY\_ID"/>
\<leftouterjoin alias1="PROV" col1="ID" condition="=" alias2="CLAI" col2="PROVIDER\_ID"/>
\</joins>
<where>
\<condition alias1="CFTY" col1="CODE" operator="=" string="INSTITUTIONAL" number="" date= "" />
</where>
</functionalView>'
)
,
NULL
);
I dont think schema_viewdef is being updated with schema based xml document as viewdef and schema_vewdef holds same value after procedure execution
My registred xsd is as below:
DECLARE
l_schema CLOB;
BEGIN
l_schema := '<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="functionalView">
\<xs:complexType>
\<xs:sequence>
\<xs:element name="columns">
\<xs:complexType>
\<xs:sequence>
\<xs:element maxOccurs="unbounded" name="column">
\<xs:complexType>
\<xs:attribute name="name" type="xs:string" use="required" />
\<xs:attribute name="columnAlias" type="xs:string" use="required" />
\<xs:attribute name="description" type="xs:string" use="required" />
\</xs:complexType>
\</xs:element>
\</xs:sequence>
\</xs:complexType>
\</xs:element>
\<xs:element name="fromviews">
\<xs:complexType>
\<xs:sequence>
\<xs:element maxOccurs="unbounded" name="fromview">
\<xs:complexType>
\<xs:attribute name="name" type="xs:string" use="required" />
\<xs:attribute name="alias" type="xs:string" use="required" />
\</xs:complexType>
\</xs:element>
\</xs:sequence>
\</xs:complexType>
\</xs:element>
\<xs:element name="joins">
\<xs:complexType>
\<xs:sequence>
\<xs:choice maxOccurs="unbounded">
\<xs:element name="join">
\<xs:complexType>
\<xs:attribute name="alias1" type="xs:string" use="required" />
\<xs:attribute name="col1" type="xs:string" use="required" />
\<xs:attribute name="condition" type="xs:string" use="required" />
\<xs:attribute name="alias2" type="xs:string" use="required" />
\<xs:attribute name="col2" type="xs:string" use="required" />
\</xs:complexType>
\</xs:element>
\<xs:element name="leftouterjoin">
\<xs:complexType>
\<xs:attribute name="alias1" type="xs:string" use="required" />
\<xs:attribute name="col1" type="xs:string" use="required" />
\<xs:attribute name="condition" type="xs:string" use="required" />
\<xs:attribute name="alias2" type="xs:string" use="required" />
\<xs:attribute name="col2" type="xs:string" use="required" />
\</xs:complexType>
\</xs:element>
\</xs:choice>
\</xs:sequence>
\</xs:complexType>
\</xs:element>
\<xs:element name="where">
\<xs:complexType>
\<xs:sequence>
\<xs:element name="condition">
\<xs:complexType>
\<xs:attribute name="alias1" type="xs:string" use="required" />
\<xs:attribute name="col1" type="xs:string" use="required" />
\<xs:attribute name="operator" type="xs:string" use="required" />
\<xs:attribute name="string" type="xs:string" use="required" />
\<xs:attribute name="number" type="xs:string" use="required" />
\<xs:attribute name="date" type="xs:string" use="required" />
\</xs:complexType>
\</xs:element>
\</xs:sequence>
\</xs:complexType>
\</xs:element>
\</xs:sequence>
\<xs:attribute name="name" type="xs:string" use="required" />
\<xs:attribute name="description" type="xs:string" use="required" />
\</xs:complexType>
</xs:element>
</xs:schema>';
DBMS_XMLSCHEMA.registerSchema(schemaurl => 'functional_view_generator.xsd',
schemadoc => l\_schema,
local => TRUE,
gentypes => FALSE,
gentables => FALSE,
enablehierarchy => DBMS\_XMLSCHEMA.enable\_hierarchy\_none);
END;
Thanks,
Rajneesh