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. The I am trying to validate that 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(but you can not use it like this on LiveSQL):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 that I 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 against the provided .xsd xchema. With the second methode that I use(schemaValidate) the result that I get is 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.
XML isn't a strong point of mine :-) but (I think) for a trailing "Z", you need to let the database know that you are dealing with a time zone
http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb05sto.htm SQL> create table XML_DATE_TEST(
2 xml_file xmltype
3 );
Table created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE P_XML_DATE_TEST (p_testvar in number)
2 IS
3
4 xml_help_variable xmltype;
5
6 BEGIN
7
8 SELECT XMLELEMENT
9 ("DocumentROOTTag",
10 XMLATTRIBUTES(
11 'http://www.w3.org/2001/XMLSchema-instance' "xmlns:xsi"
12 , 'XSD_TEST.xsd' "xsi:noNamespaceSchemaLocation"),
13 XMLELEMENT
14 ("SomeDateTag",
15 (to_char( sysdate,'yyyy-mm-dd')||'T'||to_char( sysdate,'hh24:mi:ss')||'Z'))
16 )
17 INTO xml_help_variable
18 FROM dual
19 WHERE p_testvar = 2;
20
21 INSERT INTO XML_DATE_TEST VALUES (xml_help_variable);
22
23 END P_XML_DATE_TEST;
24 /
Procedure created.
SQL>
SQL> exec DBMS_XMLSCHEMA.deleteSchema(schemaurl => 'XSD_TEST.xsd');
PL/SQL procedure successfully completed.
SQL>
SQL> DECLARE
2 l_schema CLOB;
3 BEGIN
4
5
6 l_schema := '<?xml version="1.0" encoding="UTF-8"?>
7 <!--W3C Schema generated by XMLSpy v2009 sp1 (http://www.altova.com)-->
8 <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb"> <<!!!!!!
9 <xs:element name="DocumentROOTTag">
10 <xs:complexType>
11 <xs:sequence>
12 <xs:element ref="SomeDateTag" />
13 </xs:sequence>
14 </xs:complexType>
15 </xs:element>
16 <xs:element name="SomeDateTag" xdb:SQLType="TIMESTAMP WITH TIME ZONE"> <<!!!!!!
17 <xs:simpleType>
18 <xs:restriction base="xs:dateTime"/>
19 </xs:simpleType>
20 </xs:element>
21 </xs:schema>';
22 DBMS_XMLSCHEMA.registerSchema(schemaurl => 'XSD_TEST.xsd',
23 schemadoc => l_schema,
24 local => TRUE,
25 gentypes => FALSE,
26 gentables => FALSE,
27 enablehierarchy => DBMS_XMLSCHEMA.enable_hierarchy_none);
28 END;
29 /
PL/SQL procedure successfully completed.
SQL>
SQL> BEGIN
2 P_XML_DATE_TEST(2);
3 END;
4 /
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT x.xml_file.isSchemaValid('XSD_TEST.xsd')
2 FROM XML_DATE_TEST x;
X.XML_FILE.ISSCHEMAVALID('XSD_TEST.XSD')
----------------------------------------
1
1 row selected.
SQL>
SQL> BEGIN
2 DECLARE
3 XML XMLTYPE;
4 BEGIN
5 select x.xml_file.createSchemaBasedXML('XSD_TEST.xsd')
6 INTO XML
7 from XML_DATE_TEST X;
8
9 xmltype.schemaValidate(XML);
10
11 END;
12 END;
13 /
PL/SQL procedure successfully completed.
SQL>
SQL>