none
How to use new types like datetime2 in XML SCHEMA? RRS feed

  • Question

  • I'm trying to make tests for all primitive and simple types from

    select * from sys.xml_schema_types where kind = 'P' or kind = 'S'

    with queries like

    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="sqltypes: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://schemas.microsoft.com/sqlserver/2004/sqltypes/sql2008/SqlTypes.xsd'". It exists in schema from Microsoft as "xsd:simpleType name="datetime2"", but probably I'm doing something wrong, except I don't know what is wrong exactly. How to use these types in schemas? There is example on Microsoft for SQL Server, unfortunately I can't find it today, but it was without schema header anyway (useless).

    Friday, October 11, 2019 7:40 AM

Answers

  • There is no problem with datetime2, but you would have made it easier if you had taken one of the older types, since you would have gotten the same error.
    Some googling gave me the tip to run a statement like

    SELECT * FROM tbl FOR XML AUTO, XMLSCHEMA

    This tells us how the schema should look like:

    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>';

    Note that the sqltypes attribute only has the logical path. It is the import element that has the full path.

    When it comes to inserting the value there was a validation error; there needs to be a T between date and time, space is not accepted.

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


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by s73 Friday, October 11, 2019 10:19 AM
    Friday, October 11, 2019 10:12 AM

All replies

  • XML is a Standard and in this there is no definition for datetime2; simply use datetime for XML, see https://www.w3.org/2001/sw/rdb2rdf/wiki/Mapping_SQL_datatypes_to_XML_Schema_datatypes

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, October 11, 2019 8:05 AM
  • But somehow SQL Server storing datetimeoffset inside XML (from my experience of decompressing XML from databases). So I thought these types are extensions to standard types. What the point of having them if these types cannot be used?
    Friday, October 11, 2019 8:23 AM
  • There is no problem with datetime2, but you would have made it easier if you had taken one of the older types, since you would have gotten the same error.
    Some googling gave me the tip to run a statement like

    SELECT * FROM tbl FOR XML AUTO, XMLSCHEMA

    This tells us how the schema should look like:

    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>';

    Note that the sqltypes attribute only has the logical path. It is the import element that has the full path.

    When it comes to inserting the value there was a validation error; there needs to be a T between date and time, space is not accepted.

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


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by s73 Friday, October 11, 2019 10:19 AM
    Friday, October 11, 2019 10:12 AM
  • Thanks, inserting values still "todo", I've tried dateTime with "T", with "Z" and offset, with points and "-", as "YYYY-MM-DDTHH:MM:SS" or "DD.MM.YYYYTHH:MM:SS" (from regional settings), but still have errors, from those date types was able only to insert duration as "P2Y6M5DT12H35M30S" without any clue what "P" means.
    Friday, October 11, 2019 10:30 AM
  • Hi s73, Olaf, and Erland,

    XSD 1.1 supports additional datatypes. dateTimeStamp has been added. Just the same as dateTime but a timezone is required.

    Unfortunately, MS SQL Server is still on XSD 1.0, circa 2001.

    I recently asked Microsoft to beef up the entire XML functionality in SQL Server. Please check it out, add your comments, and vote for it:

    SQL Server vNext (post 2019) and NoSQL functionality



    Friday, October 11, 2019 1:27 PM
  • I guess the problem for me was dateTime as "2019-09-15T1:23:45" without zero. As "2019-09-15T01:23:45" it worked. Or something else due to schema header was simple without Microsoft's stuff. About XML schemas - I do that only because I need to decompress XML data back to original query state to work with SQL Server log. After that I'm hoping to forget about it and never touch it again, everything like sysadmins made for sysadmins with AutoDoc documentation.

    Was asking the same question on stackoverflow and nobody had any clue - it already says something about XML in SQL Server.

    Friday, October 11, 2019 2:49 PM