• Статья

Использование 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-данных