Oracle Timestamps in xml

timestamps_in_xml_with_schema

DECLARE
  num_Check    NUMBER;
BEGIN
  SELECT SIGN(COUNT(*))
  INTO   num_Check
  FROM   ALL_XML_SCHEMAS
  WHERE  OWNER = 'AIS_NET'
  AND    SCHEMA_URL = 'http://localhost/xdb/latera/hydra/provisioning/timewrapper.xsd';
  
  IF num_Check > 0 THEN
    DBMS_XMLSCHEMA.DELETESCHEMA(
      schemaurl => 'http://localhost/xdb/latera/hydra/provisioning/timewrapper.xsd');
  END IF;
  
  DBMS_XMLSCHEMA.registerSchema(
    SCHEMAURL => 'http://localhost/xdb/latera/hydra/provisioning/timewrapper.xsd',
    SCHEMADOC => '
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb">
  <xsd:complexType name="timewrapper" xdb:SQLType="timewrapper">
    <xsd:sequence>
      <xsd:element name="n_line_id" type="xsd:decimal" xdb:SQLType="NUMBER"/>
      <xsd:element name="ts_sometime" type="xsd:dateTime" xdb:SQLType="TIMESTAMP"/>
    </xsd:sequence>
  </xsd:complexType>
  <xsd:element name="timewrapper" type="timewrapper" xdb:SQLType="timewrapper"/>
</xsd:schema>', 
    GENTYPES  => TRUE,
    GENTABLES => FALSE,
    OWNER     => 'AIS_NET');
END;
/
desc "timewrapper"
/
CREATE OR REPLACE TYPE "timewrapper" FORCE AS OBJECT(
  "SYS_XDBPD$"                        "XDB"."XDB$RAW_LIST_T",
  "n_line_id"                         NUMBER,
  "ts_sometime"                       TIMESTAMP,
  
  -- Converts to xml with all safety escapes.
  -- %return XML string
  MEMBER FUNCTION SERIALIZE
  RETURN CLOB,

  -- Deserialize from XML.
  -- %param clb_CL_SERIALIZED_EQUIPMENT Serialized equipment
  -- %return Restored object
  STATIC FUNCTION DESERIALIZE(
    clb_CL_SERIALIZED_WRAPPER         CLOB)
  RETURN "timewrapper",
  
  -- XML schema url.
  STATIC FUNCTION SCHEMA_URL
  RETURN VARCHAR2,
  
  -- Root element of XML schema.
  STATIC FUNCTION SCHEMA_ELEMENT
  RETURN VARCHAR2)
/
CREATE OR REPLACE TYPE BODY "timewrapper" AS
  -- =============================================================================
  -- Converts to xml with all safety escapes.
  MEMBER FUNCTION SERIALIZE
  RETURN CLOB
  AS
  BEGIN
    RETURN XMLType(
             xmlData => SELF,
             schema  => "timewrapper".SCHEMA_URL,
             element => "timewrapper".SCHEMA_ELEMENT).getClobVal();
  END SERIALIZE;
  -- =============================================================================
  -- Deserialize from XML.
  STATIC FUNCTION DESERIALIZE(
    clb_CL_SERIALIZED_WRAPPER       CLOB)
  RETURN "timewrapper"
  AS
    obj_Timewrapper                     "timewrapper";
  BEGIN
    IF clb_CL_SERIALIZED_WRAPPER IS NULL THEN
      RETURN NULL;
    END IF;

    XMLType(clb_CL_SERIALIZED_WRAPPER).toObject(
                                         object  => obj_Timewrapper,
                                         schema  => "timewrapper".SCHEMA_URL,
                                         element => "timewrapper".SCHEMA_ELEMENT);

    RETURN obj_Timewrapper;
  END DESERIALIZE;
  -- =============================================================================
  -- XML schema url.
  STATIC FUNCTION SCHEMA_URL
  RETURN VARCHAR2
  IS
  BEGIN
    RETURN 'http://localhost/xdb/latera/hydra/provisioning/timewrapper.xsd';
  END SCHEMA_URL;
  -- =============================================================================
  -- Root element of XML schema.
  STATIC FUNCTION SCHEMA_ELEMENT
  RETURN VARCHAR2
  IS
  BEGIN
    RETURN 'timewrapper';
  END SCHEMA_ELEMENT;
END;
/
declare
  obj_W        "timewrapper";
  clb_Message  CLOB;
  --
  obj_New      "timewrapper";
  clb_New      CLOB;
begin
  obj_W := "timewrapper"(null, 1, systimestamp);
  
  clb_Message := obj_W.serialize;
  
  obj_New := "timewrapper".deserialize(clb_Message);
  clb_New := obj_New.SERIALIZE;
  
  IF clb_New = clb_Message THEN
    RAISE_APPLICATION_ERROR(-20100, 'same'); -- they are the same so oracle adds milliseconds and parses them correctly
  ELSE
    RAISE_APPLICATION_ERROR(-20100, 'not same');
  END IF;
end;
/