發行項
04/02/2012

搭配 time、date、datetime2 和 datetimeoffset 資料類型使用 XML

現有的 XML 結構描述文件 sqltypes.xsd 會描述 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	`<xsd:simpleType name="datetime"> <xsd:restriction base="xsd:dateTime"> ....<xsd:pattern value="((000[1-9])\|(00[1-9][0-9])\|(0[1-9][0-....9]{2})\|([1-9][0-9]{3}))-((0[1-9])\|(1[012]))-((0[1-9])\|([12][0-....9])\|(3[01]))T(([01][0-9])\|(2[0-3]))(:[0-5][0-9]){2}(\.[0-....9]{2}[037])?" /> <xsd:maxInclusive value="9999-12-31T23:59:59.997" /> <xsd:minInclusive value="1753-01-01T00:00:00.000" /> </xsd:restriction> </xsd:simpleType>`
smalldatetime	`<xsd:simpleType name="smalldatetime"> <xsd:restriction base="xsd:dateTime"> <xsd:pattern value="((000[1-9])\|(00[1-9][0-9])\|(0[1-9][0-9]{2})\|([1-9][0-9]{3}))-((0[1-9])\|(1[012]))-((0[1-9])\|([12][0-9])\|(3[01]))T(([01][0-9])\|(2[0-3]))(:[0-5][0-9])(:00)" /> <xsd:maxInclusive value="2079-06-06T23:59:00" /> <xsd:minInclusive value="1900-01-01T00:00:00" /> </xsd:restriction> </xsd:simpleType>`
date	`<xsd:simpleType name="date"> <xsd:restriction base="xsd:date"> <xsd:pattern value="((000[1-9])\|(00[1-9][0-9])\|(0[1-9][0-9]{2})\|([1-9][0-9]{3}))-((0[1-9])\|(1[012]))-((0[1-9])\|([12][0-9])\|(3[01]))" /> <xsd:maxInclusive value="9999-12-31" /> <xsd:minInclusive value="0001-01-01" /> </xsd:restriction> </xsd:simpleType>`
time	`xsd:simpleType name="time"> <xsd:restriction base="xsd:time"> <xsd:pattern value="(([01][0-9])\|(2[0-3]))(:[0-5][0-9]){2}(\.[0-9]+)?" /> <xsd:annotation> <xsd:minInclusive value="00:00:00" /> </xsd:annotation> </xsd:restriction> </xsd:simpleType>`
datetime2	`<xsd:simpleType name="datetime2"> <xsd:restriction base="xsd:dateTime"> <xsd:pattern value="((000[1-9])\|(00[1-9][0-9])\|(0[1-9][0-9]{2})\|([1-9][0-9]{3}))-((0[1-9])\|(1[012]))-((0[1-9])\|([12][0-9])\|(3[01]))T(([01][0-9])\|(2[0-3]))(:[0-5][0-9]){2}(\.[0-9]+)?" /> <xsd:annotation> <xsd:minInclusive value="0001-01-01T00:00:00" /> </xsd:annotation> </xsd:restriction> </xsd:simpleType>`
datetimeoffset	`<xsd:simpleType name="DATETIMEOFFSET"> <xsd:restriction base="xsd:dateTime" /> <xsd:pattern value="((000[1-9])\|(00[1-9][0-9])\|(0[1-9][0-9]{2})\|([1-9][0-9]{3}))-((0[1-9])\|(1[012]))-((0[1-9])\|([12][0-9])\|(3[01]))T(([01][0-9])\|(2[0-3]))(:[0-5][0-9]){2}(\.[0-9]+)?(([\+\|\-]((0[0-9])\|(1[0-2]))(:[0-5][0-9]))\|(\+13(:[0-5][0-9])(:[0-5][0-9]))\|\+14:00\|Z)"(([\+\|\-]((((0[0-9])\|(1[0-3]))(:[0-5][0-9]))\|14:00))\|Z)" /> <xsd:minInclusive value="0001-01-01T00:00:00Z" /> </xsd:restriction> </xsd:simpleType>`

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 值執行個體都可以轉換成 date、datetime、smalldatetime、datetime2、datetimeoffset 或 SQL Server 中的任何字元類型。
表示時間的任何 XQuery 值執行個體都可以轉換成 time、datetime、smalldatetime、datetime2、datetimeoffset 或 SQL Server 中的任何字元類型。
表示日期和時間且不含時區的任何 XQuery 值執行個體都可以轉換成 datetime、smalldatetime、datetimeoffet (含 0 時差)、date (時間部分被捨棄)、time (日期部分被捨棄)、datetime2, 或 SQL Server 中的任何字元類型。
表示日期和時間 (具有 Z 或時區時差 (+|-hh:mm)) 的任何 XQuery 值執行個體都可以轉換成 datetime (不含時差的 UTC)、smalldatetime (不含時差的 UTC)、datetimeoffet、date (時間部分和時差被捨棄)、datetime2 (不含時差的 UTC)、time (日期部分和時差被捨棄) 或是 SQL Server 中的任何字元類型。
如果轉換導致溢位或是超出範圍的狀況，將會傳回一則錯誤訊息。
如果日期、時間或是包含時間值的日期 (不論有沒有時區) 的有效位數 (小數秒數) 多於目標類型，則較大的小數秒數有效位數將會被捨入。
表示 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 中，日期和時間類型之 XML 執行個體的儲存格式會因為使用新的 SQL Server 日期和時間類型而變更。下表顯示 XSD 到 SQL Server 資料類型對應。此對應也會決定 sql:column() 和 sql:variable() 與 XML 日期和時間執行個體的傳回結果之間的作業語意。

XSD 類型	XML 儲存 SQL Server 類型對應 (包含 TZ)	XML 儲存 SQL Server 類型對應 (不含 TZ)
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

範例

下列範例會搭配 FOR XML 使用 date、time 和 datetimeoffset 類型使用。

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 內嵌結構描述

當使用 FOR XML 子句套用 XMLSCHEMA 子句時，產生的 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

請參閱

概念

使用 XML 資料