对 time、date、datetime2 和 datetimeoffset 数据类型使用 XML
现有的 XML 架构文档 sqltypes.xsd 介绍了 W3C XML 架构类型，W3C XML 架构类型用于在 FOR XML 和 HTTP/SOAP 中描述 SQL Server 数据类型。
sqltypes.xsd 架构文档
2004 XML 架构命名空间经过扩展，包括下列 SQL Server 数据类型：time、date、datetime2 和 datetimeoffset。
.NET Framework System.Data.SqlDbType
SqlDbTypeEnum 中添加了下列枚举。
SQL Server 类型 |
.NET Framework SqlDbType |
|---|---|
date |
Date |
time |
Time |
datetime2 |
LargeDateTime |
datetimeoffset |
DateTimeWithTimeZone |
完整的 SqlDbTypeEnum 如下：
<xsd:simpleType name="sqlDbTypeEnum">
<xsd:restriction base="xsd:string">
<xsd:enumeration value="BigInt" />
<xsd:enumeration value="Binary" />
<xsd:enumeration value="Bit" />
<xsd:enumeration value="Char" />
<xsd:enumeration value="Date" />
<xsd:enumeration value="DateTime" />
<xsd:enumeration value="DateTimeWithTimeZone" />
<xsd:enumeration value="Decimal" />
<xsd:enumeration value="Float" />
<xsd:enumeration value="Image" />
<xsd:enumeration value="Int" />
<xsd:enumeration value="LargeDateTime" />
<xsd:enumeration value="Money" />
<xsd:enumeration value="NChar" />
<xsd:enumeration value="NText" />
<xsd:enumeration value="NVarChar" />
<xsd:enumeration value="Real" />
<xsd:enumeration value="SmallDateTime" />
<xsd:enumeration value="SmallInt" />
<xsd:enumeration value="SmallMoney" />
<xsd:enumeration value="Text" />
<xsd:enumeration value="Time" />
<xsd:enumeration value="Timestamp" />
<xsd:enumeration value="TinyInt" />
<xsd:enumeration value="Udt" />
<xsd:enumeration value="UniqueIdentifier" />
<xsd:enumeration value="VarBinary" />
<xsd:enumeration value="VarChar" />
<xsd:enumeration value="Variant" />
<xsd:enumeration value="Xml" />
</xsd:restriction>
</xsd:simpleType>
SQL Server 日期和时间数据类型的说明和映射
下表列出了 SQL Server 2008 中新增的日期和时间数据类型的架构定义。
数据类型 |
架构定义 |
|---|---|
datetime |
|
smalldatetime |
|
date |
|
time |
|
datetime2 |
|
datetimeoffset |
|
XML 架构命名空间系统目录
现有 2004 XML 架构命名空间中新增加的日期和时间类型信息会填充到以下 XML 架构特有的系统目录中，并且可以从这些目录中进行查询：
Sys.xml_schemla_namespaces
Sys.xml_schema_collections
Sys.xml_schema_components
XML value() 方法
XML value() 方法提供了指示所提取的值的 SQL Server 数据类型的方法。这暗含一个从 XSD 值到指定类型的 SQL Server 值的转换。SQL Server 日期和时间类型支持下列转换：
任何表示日期的 XQuery 值实例都可以转换为 SQL Server 中的 date、datetime、smalldatetime、datetime2、datetimeoffset 或任何字符类型。
任何表示时间的 XQuery 值实例都可以转换为 SQL Server 中的 time、datetime、smalldatetime、datetime2、datetimeoffset 或任何字符类型。
任何表示日期（具有时间但没有时区的）的 XQuery 值实例都可以转换为 SQL Server 中的 datetime、smalldatetime、datetimeoffet（时区偏移量为 0）、date（时间部分被删除）、time（日期部分被删除）、datetime2, 或任何字符类型。
任何表示日期（具有时间，且具有 Z 或时区偏移量 (+|-hh:mm)）的 XQuery 值实例都可以转换为 SQL Server 中的 datetime（没有时区偏移量的 UTC）、smalldatetime（没有时区偏移量的 UTC）、datetimeoffet、date（时间部分和时区偏移量被删除）、datetime2（没有时区偏移量的 UTC）、time（日期部分和时区偏移量被删除）或任何字符类型。
如果转换导致溢出或超出范围的情况，将返回错误消息。
如果日期、时间或带时间值（有或没有时区）的日期具有比目标类型更高的精度（秒的小数部分），则秒的小数部分具有较高精度的日期将四舍五入。
表示 date 数据类型的任何 XML 值实例的日期格式都由 SET LANGUAGE 和 SET DATEFORMAT 设置决定。
示例
下面的示例使用 value() 方法。
DECLARE @myDoc xml;
DECLARE @OrderID int;
DECLARE @OrderDate date;
DECLARE @OrderTime time;
DECLARE @OrderDateTime datetimeoffset;
SET @myDoc = '<Root>
<OrderDescription OrderID="1" OrderDate="1999-12-20" OrderTime="13:40:58.47786" OrderDateTime="1999-12-20 13:40:58.123-05:00">
<Features>
<Warranty>1 year parts and labor</Warranty>
<Maintenance>3 year parts and labor extended maintenance is available</Maintenance>
</Features>
</OrderDescription>
</Root>';
SET @OrderID = @myDoc.value('(/Root/OrderDescription/@OrderID)[1]', 'int');
SET @OrderDate = @myDoc.value('(/Root/OrderDescription/@OrderDate)[1]', 'date');
SET @OrderTime = @myDoc.value('(/Root/OrderDescription/@OrderTime)[1]', 'time');
SET @OrderDateTime = @myDoc.value('(/Root/OrderDescription/@OrderDateTime)[1]', 'datetimeoffset');
SELECT @OrderID,@OrderDate,@OrderTime,@OrderDateTime;
--Returns: 1 1999-12-20 13:40:58.4778600 1999-12-20 13:40:58.1230000 -0
XQuery 伪函数
sql:column() 和 sql:variable() 识别 date、time、datetime2 和 datetimeoffset 数据类型。
SQL Server 到 XSD 的类型映射
对于 sql:variable() 和 sql:column()，从 SQL Server 值转换的 Xquery 值的 Xquery 基类型由 SQL Server 到 XSD 的类型映射语义来决定。此映射语义在扩展的 XML 架构命名空间中定义。
SQL Server 类型 |
XSD 类型 |
|---|---|
date |
xsd:date |
time |
xsd:time |
datetime2 |
xsd:datetime |
datetimeoffset |
xsd:datetime |
XML 存储格式类型映射
在 SQL Server 2008 中，通过使用新的 SQL Server 日期和时间类型来更改日期和时间类型的 XML 实例的存储格式。下表显示 XSD 到 SQL Server 的数据类型映射。此映射还决定了 sql:column() 和 sql:variable() 的返回结果与 XML 日期和时间实例之间的操作语义。
XSD 类型 |
带 TZ 的 XML 存储 SQL Server 类型映射 |
不带 TZ 的 XML 存储 SQL Server 类型映射 |
|---|---|---|
xs:date |
datetimeoffset |
date |
xs:time |
datetimeoffset |
datetimeoffset |
xs:dateTime |
datetimeoffset |
datetime2 |
FOR XML 和 XMLSCHEMA 子句
当在 SELECT 语句中指定 FOR XML 或 XMLSCHEMA 子句时，支持 date、time、datetime2 和 datetimeoffset 数据类型所声明的 SQL Server 列。
输出格式
下表列出了 date、time、datetime2 和 datetimeoffset 数据类型的 FOR XML 输出格式。
SQL Server 类型 |
FOR XML 输出格式 |
|---|---|
date |
YYYY-MM-DD |
time |
hh:mm:ss[.nnnnnnn] |
datetime2 |
YYYY-MM-DDThh:mm:ss[.nnnnnnn] |
datetimeoffset |
YYYY-MM-DDThh:mm:ss[.nnnnnnn] [+|-]hh:mm |
示例
下面的示例将 date、time 和datetimeoffset 类型与 FOR XML 一起使用。
CREATE TABLE T1
(
dt date, tm time, dtz datetimeoffset
);
GO
INSERT INTO T1
VALUES('1996-12-16', '12:30:47.7867', '1996-12-16 12:30:47.7867-05:00');
SELECT dt FROM T1 FOR XML AUTO;
--Returns:
--XML_F52E2B61-18A1-11d1-B105-00805F49916B
------------------------------------------
--<t1 dt="1996-12-16"/>
SELECT tm FROM T1 FOR XML AUTO;
--Returns:
--XML_F52E2B61-18A1-11d1-B105-00805F49916B
------------------------------------------
--<t1 tm="12:30:47.7867"/>
SELECT dtz FROM T1 FOR XML AUTO;
--Returns:
--XML_F52E2B61-18A1-11d1-B105-00805F49916B
------------------------------------------
--<t1 dtz="1996-12-16T12:30:47.7867 -05:00"/>
具有 XMLSCHEMA 子句的 XSD 内联架构
当 XMLSCHEMA 子句与 FOR XML 子句一起应用时，生成的 XSD 内联架构遵循为扩展的现有 XML 架构命名空间中的每个新增日期和时间类型定义的模式限制方法。
OPENXML () 函数
下表列出了 date、time、datetime2 和 datetimeoffset 数据类型的 OPENXML 输入格式。
SQL Server 类型 |
FOR XML 输出格式 |
|---|---|
date |
YYYY-MM-DD |
time |
hh:mm:ss[.nnnnnnn] |
datetime2 |
YYYY-MM-DDThh:mm:ss[.nnnnnnn] |
datetimeoffset |
YYYY-MM-DDThh:mm:ss[.nnnnnnn][+|-]hh:mm |
示例
下面的示例对 datetimeoffset 数据类型使用 OPENXML。
CREATE TABLE T1
(
dt date, tm time(7), dtz datetimeoffset(7)
)
GO
DECLARE @docHandle int;
DECLARE @xmlDocument nvarchar(max); -- or xml type
SET @xmlDocument = N'<ROOT>
<T1 dt="2000-08-25" tm="12:30:47.1234567" dtz="2000-08-25T05:22:36.1234567-05:00"/>
</ROOT>';
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument;
-- Use OPENXML to provide rowset that consists of customer data.
INSERT T1
SELECT *
FROM OPENXML(@docHandle, N'/ROOT/T1')
WITH T1;
-- Using OPENXML in a SELECT statement
SELECT * FROM OPENXML(@docHandle, N'/ROOT/T1') WITH (dt date , tm time, dtz datetimeoffset);
EXEC sp_xml_removedocument @docHandle