[アーティクル]
09/28/2010

time、date、datetime2、および datetimeoffset データ型での XML の使用

既存の XML スキーマドキュメント sqltypes.xsd には、SQL Server データ型を FOR XML および HTTP/SOAP で記述するために使用される W3C XML スキーマ型が記述されています。

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 型	TZ を含む XML ストレージの SQL Server 型マッピング	TZ を含まない XML ストレージの SQL Server 型マッピング
xs:date	datetimeoffset	date
xs:time	datetimeoffset	datetimeoffset
xs:dateTime	datetimeoffset	datetime2

FOR XML 句と XMLSCHEMA 句

FOR XML 句または XMLSCHEMA 句が SELECT ステートメントで指定されている場合は、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"/>

XSD インラインスキーマと XMLSCHEMA 句

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

次の方法で共有

time、date、datetime2、および datetimeoffset データ型での XML の使用

sqltypes.xsd スキーマドキュメント

NET Framework System.Data.SqlDbType

SQL Server の日付と時刻のデータ型の説明とマッピング

XML スキーマ名前空間のシステムカタログ

XML value() メソッド

例

XQuery 擬似関数

SQL Server から XSD への型マッピング

XML ストレージ形式の型マッピング

FOR XML 句と XMLSCHEMA 句

出力形式

例

XSD インラインスキーマと XMLSCHEMA 句

OPENXML () 関数

例

関連項目

概念

その他のリソース

次の方法で共有

time、date、datetime2、および datetimeoffset データ型での XML の使用

sqltypes.xsd スキーマ ドキュメント

NET Framework System.Data.SqlDbType

SQL Server の日付と時刻のデータ型の説明とマッピング

XML スキーマ名前空間のシステム カタログ

XML value() メソッド

例

XQuery 擬似関数

SQL Server から XSD への型マッピング

XML ストレージ形式の型マッピング

FOR XML 句と XMLSCHEMA 句

出力形式

例

XSD インライン スキーマと XMLSCHEMA 句

OPENXML () 関数

例

関連項目

概念

その他のリソース

sqltypes.xsd スキーマドキュメント

XML スキーマ名前空間のシステムカタログ

XSD インラインスキーマと XMLSCHEMA 句