• Artigo

Usando XML com tipos de dados de hora, data, datetime2 e datetimeoffset

O documento de esquema XML existente sqltypes.xsd descreve os tipos de esquema W3C XML usados para descrever os tipos de dados do SQL Server em FOR XML e HTTP/SOAP.

Documento de esquema sqltypes.xsd

O namespace do esquema 2004 XML foi estendido para incluir os seguintes tipos de dados do SQL Server:hora, data, datetime2 e datetimeoffset.

NET Framework System.Data.SqlDbType

As enumerações a seguir são adicionadas ao SqlDbTypeEnum.

Tipo de SQL Server

.NET Framework SqlDbType

date

Date

time

Time

datetime2

LargeDateTime

datetimeoffset

DateTimeWithTimeZone

O SqlDbTypeEnum completo é como segue:

  <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>

Descrição e mapeamento de tipos de dados de data e hora do SQL Server

A tabela a seguir lista as definições de esquema para os tipos de dados de data e hora que são novos no SQL Server 2008.

Tipo de dados

Definição de esquema

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>

Catálogos do sistema de namespace do esquema XML

As novas informações de tipo de data e hora adicionadas ao namespace do esquema 2004 XML existente são populadas e podem ser consultadas nos seguintes catálogos do sistema específicos do esquema XML:

  • Sys.xml_schemla_namespaces

  • Sys.xml_schema_collections

  • Sys.xml_schema_components

Método XML value()

O método XML value() fornece um modo para indicar o tipo de dados do SQL Server do valor extraído. Isso implica uma conversão do valor XSD em um valor do SQL Server para o tipo especificado. Os tipos de data e hora do SQL Server têm suporte para as seguintes conversões:

  • Qualquer instância de valor XQuery que representa uma data pode ser convertida em date, datetime, smalldatetime, datetime2, datetimeoffset ou qualquer tipo de caractere no SQL Server.

  • Qualquer instância de valor XQuery que representa uma hora pode ser convertida em time, datetime, smalldatetime, datetime2, datetimeoffset ou qualquer tipo de caractere no SQL Server.

  • Qualquer instância de valor XQuery que representa uma data com hora e sem fuso horário pode ser convertida em datetime, smalldatetime, datetimeoffet (com deslocamento de fuso horário 0), date (parte da hora que está sendo descartada), time (parte da data que está sendo descartada), datetime2, ou qualquer tipo de caractere no SQL Server.

  • Qualquer instância de valor XQuery que representa uma data com hora Z ou deslocamento de fuso horário (+|-hh:mm) pode ser convertida em datetime (UTC sem deslocamento de fuso horário), smalldatetime (UTC sem deslocamento de fuso horário), datetimeoffet, date (deslocamento de fuso horário e parte da hora é descartado), datetime2 (UTC sem deslocamento de fuso horário), time (deslocamento de fuso horário e parte da data é descartado) ou qualquer tipo de caractere no SQL Server.

  • Se a conversão conduzir a uma condição de estouro ou fora do intervalo, uma mensagem de erro será retornada.

  • Se uma data, hora ou data com valor de hora (com ou sem fuso horário) for mais precisa (frações de segundo) do que o tipo de destino, a precisão maior de frações de segundo será arredondada.

  • O formato de data de qualquer instância de valor XML que representa um tipo de dados de date é determinado pelas configurações de SET LANGUAGE e SET DATEFORMAT.

Exemplo

O exemplo a seguir usa o método 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

Pseudofunções XQuery

sql:column() e sql:variable() reconhecem os tipos de dados date, time, datetime2 e datetimeoffset.

Mapeamento de tipo SQL Server para XSD

Para sql:variable() e sql:column(), o tipo de base Xquery do valor Xquery que é transformado do valor do SQL Server é determinado pelo SQL Server para a semântica de mapeamento de tipo XSD. Essa semântica de mapeamento é definida no namespace do esquema XML estendido.

Tipo de SQL Server

Tipo de XSD

date

xsd:date

time

xsd:time

datetime2

xsd:datetime

datetimeoffset

xsd:datetime

Mapeamento de tipo de formato de armazenamento XML

No SQL Server 2008, o formato de armazenamento para a instância XML das alterações de tipos de data e hora ao usar os novos tipos de data e hora do SQL Server. A tabela a seguir mostra o XSD para mapeamento de tipo de dados do SQL Server. Esse mapeamento também determinará a semântica da operação entre os resultados de retorno do sql:column() e do sql:variable() e as instâncias de data e hora XML.

Tipo de XSD

Mapeamento de tipo SQL Server de armazenamento XML com TZ

Mapeamento de tipo SQL Server de armazenamento XML sem TZ

xs:date

datetimeoffset

date

xs:time

datetimeoffset

datetimeoffset

xs:dateTime

datetimeoffset

datetime2

Cláusulas FOR XML e XMLSCHEMA

Colunas do SQL Server que são declaradas pelos tipos de dados date, time, datetime2 e datetimeoffset têm suporte quando a cláusula FOR XML ou XMLSCHEMA é especificada na instrução SELECT.

Formato de saída

A tabela a seguir lista formatos de saída FOR XML para os tipos de dados date, time, datetime2 e datetimeoffset.

Tipo de SQL Server

Formato de saída FOR XML

date

AAAA-MM-DD

time

hh:mm:ss[.nnnnnnn]

datetime2

AAAA-MM-DDThh:mm:ss[.nnnnnnn]

datetimeoffset

AAAA-MM-DDThh:mm:ss[.nnnnnnn] [+|-]hh:mm

Exemplo

O exemplo a seguir usa os tipos date, time e datetimeoffset com 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"/>

Esquema XSD embutido com a cláusula XMLSCHEMA

Quando a cláusula XMLSCHEMA é aplicada com a cláusula FOR XML, o esquema embutido XSD gerado segue os métodos de restrição padrão definidos para cada novo tipo de data e hora no namespace do esquema XML estendido existente.

Função OPENXML ()

A tabela a seguir lista os formatos de entrada OPENXML para os tipos de dados date, time, datetime2 e datetimeoffset.

Tipo de SQL Server

Formato de saída FOR XML

date

AAAA-MM-DD

time

hh:mm:ss[.nnnnnnn]

datetime2

AAAA-MM-DDThh:mm:ss[.nnnnnnn]

datetimeoffset

AAAA-MM-DDThh:mm:ss[.nnnnnnn][+|-]hh:mm

Exemplo

O exemplo a seguir usa OPENXML com o tipo de dados datetimeoffset.

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

Consulte também

Conceitos

Usando dados XML