Oracle-对照XSD在XML中验证日期格式 (yyyy-mm-ddThh24:mi:ssZ)

Oracle version:

这个查询的结果select * 从v $ version; 是:

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:

我正在用我的过程创建一个大型xmltype，并将其插入我的表中。我正在尝试验证xmltype文件。xsd，我已经注册在我的数据库。我成功地设法缩短了xmltype数据和。xsd文件，所以我可以告诉你到底是什么行里面的。xml文件我有问题。

Code that I have prepared you can copy and paste for testing(but you can not use it like this on LiveSQL):

这是我的简单表:

create table XML_DATE_TEST(
    xml_file xmltype
);

创建xmltype数据并将其插入此表中的过程是:

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;

然后我注册我的。像这样的xsd模式:

BEGIN
    DECLARE
        l_schema CLOB;
    BEGIN
        l_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;

然后我打电话给我的程序:

BEGIN
    P_XML_DATE_TEST(2);
END;

毕竟，我尝试验证从我的表中创建的xmltype数据。我注册的xsd文件。我尝试通过两种方式做到这一点:

1.通过使用isSchemaValid

SELECT x.xml_file.isSchemaValid('XSD_TEST.xsd')
FROM XML_DATE_TEST x;

2.通过使用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:

与第一个方法，我使用 (isSchemaValid)，我得到的结果是1。这意味着我的xmltype数据是正确的。xsd xchema。与第二个方法，我使用 (schemaValidate)，我得到的结果是错误的:

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:

当我从日期格式中删除 'Z' 部分时，一切正常，但这不是一个对我来说还可以的解决方案。日期的格式必须像现在这样。

XML不是我的强项 :-) 但是 (我认为) 对于尾随的 “Z”，您需要让数据库知道您正在处理时区

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 := '
  7                      
  8                         <
 10                              
 11                                  
 12                                      
 13                                  
 14                              
 15                          
 16                             <
 18                                  
 19                              
 20                          
 21                      ';
 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>