Oracle version:
The result of this query select * from v$version; is:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
"CORE 11.2.0.4.0 Production"
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
Introduction to my situation:
I am creating a large xmltype with my procedure and I insert it in my table. Then I am trying to validate that created xmltype file against .xsd that I have registred in my database. I have succesfuly managed to shorten the xmltype data and the .xsd file so I can show you exactly on what line inside of the .xml file I am having problems.
Code that I have prepared you can copy and paste for testing:
This is my simple table:
create table XML_DATE_TEST(
xml_file xmltype
);
And the procedure that is creating xmltype data and inserting it in this table is this:
CREATE OR REPLACE PROCEDURE P_XML_DATE_TEST (p_testvar in number) --
IS
xml_help_variable xmltype;
BEGIN
SELECT XMLELEMENT
("DocumentROOTTag",
XMLATTRIBUTES(
'http://www.w3.org/2001/XMLSchema-instance' "xmlns:xsi"
, 'XSD_TEST.xsd' "xsi:noNamespaceSchemaLocation"),
XMLELEMENT
("SomeDateTag",
(to_char( sysdate,'yyyy-mm-dd')||'T'||to_char( sysdate,'hh24:mi:ss')||'Z'))
)
INTO xml_help_variable
FROM dual
WHERE p_testvar = 2;
INSERT INTO XML_DATE_TEST VALUES (xml_help_variable);
END P_XML_DATE_TEST;
Then I register my .xsd schema like this:
BEGIN
DECLARE
l_schema CLOB;
BEGIN
l_schema := '<?xml version="1.0" encoding="UTF-8"?>
<!--W3C Schema generated by XMLSpy v2009 sp1 (http://www.altova.com)-->
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="DocumentROOTTag">
<xs:complexType>
<xs:sequence>
<xs:element ref="SomeDateTag"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="SomeDateTag">
<xs:simpleType>
<xs:restriction base="xs:dateTime"/>
</xs:simpleType>
</xs:element>
</xs:schema>';
DBMS_XMLSCHEMA.registerSchema(schemaurl => 'XSD_TEST.xsd',
schemadoc => l_schema,
local => TRUE,
gentypes => FALSE,
gentables => FALSE,
enablehierarchy => DBMS_XMLSCHEMA.enable_hierarchy_none);
END;
END;
And then I call my procedure:
BEGIN
P_XML_DATE_TEST(2);
END;
After all of that I go and try to validate the created xmltype data from my table against .xsd file that I have registred. I try to do it in two ways:
1.By using isSchemaValid
SELECT x.xml_file.isSchemaValid('XSD_TEST.xsd')
FROM XML_DATE_TEST x;
2.By using schemaValidate
BEGIN
DECLARE
XML XMLTYPE;
BEGIN
select x.xml_file.createSchemaBasedXML('XSD_TEST.xsd')
INTO XML
from XML_DATE_TEST X;
xmltype.schemaValidate(XML);
END;
END;
Problem:
With the first methode that I use(isSchemaValid) the result that I get is 1. That means that my xmltype data is correct when I validate it against the provided .xsd schema. With the second methode that I use(schemaValidate) the result that I get is an error:
Error report -
ORA-30992: error occurred at Xpath /DocumentROOTTag/SomeDateTag
ORA-01830: date format picture ends before converting entire input string
ORA-06512: at "SYS.XMLTYPE", line 354
ORA-06512: at line 9
30992. 00000 - "error occurred at Xpath %s"
*Cause:
*Action: See the following error and take appropriate action.
What I have tried:
When I remove the 'Z' part from the date format everything is ok but this is not a solution that is ok for me. The format of the date has to be like it is now.
