[SOLVED] How to use datetime2 type in XML SCHEMA?

How to use datetime2 type in XML SCHEMA?

I'm trying to use SQL Server XML SCHEMA types like datetime2 in collections and tables

CREATE XML SCHEMA COLLECTION [XmlValuesSchemaCollection_datetime2] AS 
'<?xml version="1.0"?>
<xsd:schema 
    xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sql2008/SqlTypes.xsd"
    attributeFormDefault="unqualified" elementFormDefault="qualified">

    <xsd:element name="datetime2" type="xsd:datetime2"/>
</xsd:schema>';
GO

CREATE TABLE XmlValuesTable_datetime2 (
    [uid] [int] IDENTITY PRIMARY KEY,
    v XML(XmlValuesSchemaCollection_datetime2) NOT NULL
);
GO

INSERT INTO XmlValuesTable_datetime2 (v)  
VALUES (N'<datetime2 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">2014-06-18 06:39:05.190</datetime2>');
GO

but I have error Reference to an undefined name 'datetime2' within namespace 'http://www.w3.org/2001/XMLSchema'. Same with type="xsd:datetime2" - error

Reference to an undefined name 'datetime2' within namespace 'http://schemas.microsoft.com/sqlserver/2004/sqltypes/sql2008/SqlTypes.xsd'

It suppose to work somehow, types described in https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/bb677236(v=sql.105)?redirectedfrom=MSDN but unfortunately I have no clue what is wrong.

Solution

Erland Sommarskog answered at msdn Solution is

CREATE XML SCHEMA COLLECTION [XmlValuesSchemaCollection_datetime2] AS 
'<?xml version="1.0"?>
<xsd:schema    xmlns:xsd="http://www.w3.org/2001/XMLSchema"
   xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes"      attributeFormDefault="unqualified" elementFormDefault="qualified">
   <xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sql2008/sqltypes.xsd"/>

   <xsd:element name="datetime2" type="sqltypes:datetime2"/>
</xsd:schema>';