Статья
04/01/2012

Использование XML с типами данных time, date, datetime2 и datetimeoffset

В существующем документе схемы XML sqltypes.xsd описываются типы схемы W3C XML, которые используются для определения типов данных SQL Server для FOR XML и HTTP/SOAP.

Документ схемы 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-схемы

Новые сведения о типе даты и времени, которые были добавлены в существующее пространство имен XML-схемы 2004, вносятся в следующие системные каталоги для 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 (с нулевым смещением часового пояса), date (с удаленным компонентом времени), time (с удаленным компонентом даты), datetime2 или к любому символьному типу в SQL Server.
Любой экземпляр значения XQuery, представляющий дату со временем, имеющую признак часового пояса Z или смещение часового пояса (+|-чч:мм), может быть приведен к типу datetime (время в формате UTC без смещения часового пояса), smalldatetime (время в формате UTC без смещения часового пояса), datetimeoffet, date (без компонентов времени и смещения), datetime2 (время в формате UTC без смещения часового пояса), time (без компонентов даты и смещения) или к любому символьному типу в SQL Server.
Если приведение вызывает переполнение или выходит за диапазон допустимых значений, выводится сообщение об ошибке.
Если значение даты, времени или даты со временем (при наличии и отсутствии часового пояса) имеет точность (в долях секунды) большую, чем у целевого типа, значения с большей точностью долей секунды будут округляться.
Формат даты любого экземпляра XML-значения, представляющего тип данных date, определяется настройками 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() базовый тип Xquery значения Xquery, которое было преобразовано из значения SQL Server, определяется семантикой сопоставления типов 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 с часовым поясом	Сопоставление типов форматов хранения XML и SQL Server без часового пояса
xs:date	datetimeoffset	date
xs:time	datetimeoffset	datetimeoffset
xs:dateTime	datetimeoffset	datetime2

Предложения FOR XML и XMLSCHEMA

Столбцы SQL Server, объявленные типами данных date, time, datetime2 и datetimeoffset, поддерживаются в том случае, если в инструкции SELECT было указано предложение FOR XML или XMLSCHEMA.

Формат выходных данных

В следующей таблице перечисляются форматы выходных данных FOR XML для типов данных date, time, datetime2 и datetimeoffset.

Тип SQL Server	Выходной формат FOR XML
date	ГГГГ-ММ-ДД
time	чч:мм:сс[.ннннннн]
datetime2	ГГГГ-ММ-ДДТчч:мм:сс[.nnnnnnn]
datetimeoffset	ГГГГ-ММ-ДДТчч:мм:сс[.ннннннн] [+\|-]чч:мм

Пример

В следующем примере типы 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 ()

В следующей таблице перечисляются форматы входных данных OPENXML для типов данных date, time, datetime2 и datetimeoffset.

Тип SQL Server	Выходной формат FOR XML
date	ГГГГ-ММ-ДД
time	чч:мм:сс[.ннннннн]
datetime2	ГГГГ-ММ-ДДТчч:мм:сс[.nnnnnnn]
datetimeoffset	ГГГГ-ММ-ДДТчч:мм:сс[.ннннннн] [+\|-]чч:мм

Примеры

В следующем примере тип 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-данных

Поделиться через

Использование XML с типами данных time, date, datetime2 и datetimeoffset

Документ схемы sqltypes.xsd

.NET Framework System.Data.SqlDbType

Описание и сопоставление типов данных даты и времени SQL Server

Системные каталоги пространства имен XML-схемы

Метод XML value()

Пример

Псевдофункции XQuery

Сопоставления типов SQL Server и типу XSD

Сопоставление типов форматов хранения XML

Предложения FOR XML и XMLSCHEMA

Формат выходных данных

Пример

Встроенная схема XSD с предложением XMLSCHEMA

Функция OPENXML ()

Примеры

См. также

Основные понятия

Дополнительные ресурсы