Compartir a través de

  • Artículo

Usar XML con tipos de datos de time, date, datetime2 y datetimeoffset

En el documento de esquema XML existente sqltypes.xsd se describen los tipos de esquema XML del consorcio W3C que se utilizan para describir los tipos de datos de SQL Server en FOR XML y HTTP/SOAP.

Documento de esquema sqltypes.xsd

El espacio de nombres de esquema XML 2004 se ha extendido para incluir los siguientes tipos de datos de SQL Server:time, date, datetime2 y datetimeoffset.

System.Data.SqlDbType de .NET Framework

Las enumeraciones siguientes se agregan a SqlDbTypeEnum.

Tipo de SQL Server

SqlDbType de .NET Framework

date

Date

time

Time

datetime2

LargeDateTime

datetimeoffset

DateTimeWithTimeZone

La enumeración SqlDbTypeEnum completa es como sigue:

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

Descripción y asignación de tipos de datos de fecha y hora de SQL Server

La tabla siguiente enumera las definiciones de esquema para los tipos de datos de fecha y hora que son nuevos en SQL Server 2008.

Tipo de datos

Definición 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 de sistema de espacio de nombres de esquema XML

La nueva información de tipos de fecha y hora que se agrega en el espacio de nombres de esquema XML 2004 se rellena y se puede consultar en los siguientes catálogos de sistema específicos de esquema XML:

  • Sys.xml_schemla_namespaces

  • Sys.xml_schema_collections

  • Sys.xml_schema_components

Método value() de XML

El método value() de XML proporciona un medio para indicar el tipo de datos de SQL Server del valor extraído. Esto implica una conversión del valor XSD a un valor de SQL Server para el tipo especificado. Los tipos de fecha y hora de SQL Server se admiten para las conversiones siguientes:

  • Cualquier instancia de valor de XQuery que representa una fecha se puede convertir a un tipo date, datetime, smalldatetime, datetime2, datetimeoffset o cualquier tipo de caracteres en SQL Server.

  • Cualquier instancia de valor de XQuery que representa una hora se puede convertir a un tipo time, datetime, smalldatetime, datetime2, datetimeoffset o cualquier tipo de caracteres en SQL Server.

  • Cualquier instancia de valor de XQuery que representa una fecha con hora y sin zona horaria se puede convertir en datetime, smalldatetime, datetimeoffet (con ajuste de zona 0), date (parte de hora quitada), time (parte de fecha quitada), datetime2, o cualquier tipo de caracteres en SQL Server.

  • Cualquier instancia de valor de XQuery que representa una fecha con hora que tiene un ajuste de zona horaria o Z (+|-hh:mm) se puede convertir en datetime (UTC sin ajuste de zona), smalldatetime (UTC sin ajuste de zona), datetimeoffet, date (parte de hora y ajuste de zona quitados), datetime2 (UTC sin ajuste de zona), time (parte de fecha y ajuste de zona quitados), o cualquier tipo de caracteres en SQL Server.

  • Si la conversión conduce a un desbordamiento o a una condición de fuera de intervalo, se devolverá un mensaje de error.

  • Si un valor de fecha, hora o fecha y hora (con o sin zona horaria) tienen más precisión (fracciones de segundo) que el tipo de destino, se redondeará la precisión de fracciones de segundo mayor.

  • El formato de fecha de cualquier instancia de valores XML que representa un tipo de datos date está determinado por los valores SET LANGUAGE y SET DATEFORMAT.

Ejemplo

En el ejemplo siguiente se utiliza el 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

Pseudofunciones de XQuery

sql:column() y sql:variable() reconocen los tipos de datos date, time, datetime2 y datetimeoffset.

Asignación de tipo de SQL Server a tipo XSD

Para sql:variable() y sql:column(), el tipo base Xquery del valor de Xquery que se transforma a partir del valor de SQL Server está determinado por la semántica de asignación de SQL Server al tipo XSD. Esta semántica de asignación se define en el espacio de nombres de esquema XML extendido.

Tipo de SQL Server

Tipo XSD

date

xsd:date

time

xsd:time

datetime2

xsd:datetime

datetimeoffset

xsd:datetime

Asignación de tipo de formato de almacenamiento de XML

En SQL Server 2008, el formato de almacenamiento para la instancia XML de tipos de fecha y hora cambia utilizándose los nuevos tipos de fecha y hora de SQL Server. La tabla siguiente muestra la asignación de tipos de datos XSD a SQL Server. Esta asignación también determinará la semántica de operaciones entre los resultados devueltos de sql:column() y sql:variable() y las instancias XML de fecha y hora.

Tipo XSD

Asignación de tipo SQL Server de almacenamiento de XML con TZ

Asignación de tipo SQL Server de almacenamiento de XML sin TZ

xs:date

datetimeoffset

date

xs:time

datetimeoffset

datetimeoffset

xs:dateTime

datetimeoffset

datetime2

Cláusulas FOR XML y XMLSCHEMA

Las columnas de SQL Server declaradas por los tipos de datos date, time, datetime2 y datetimeoffset se admiten cuando se especifica la cláusula FOR XML o XMLSCHEMA en la instrucción SELECT.

Formato de salida

La tabla siguiente enumera los formatos de salida FOR XML para los tipos de datos date, time, datetime2 y datetimeoffset.

Tipo de SQL Server

Formato de salida 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

Ejemplo

En el siguiente ejemplo se utilizan los tipos date, time y datetimeoffset con 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 insertado con la cláusula XMLSCHEMA

Cuando la cláusula XMLSCHEMA se aplica con la cláusula FOR XML, el esquema XSD insertado generado sigue los métodos de restricción de modelo que se definen para cada uno de los nuevos tipos de fecha y hora en el el espacio de nombres de esquema XML existente extendido.

Función OPENXML ()

La tabla siguiente enumera los formatos de entrada OPENXML para los tipos de datos date, time, datetime2 y datetimeoffset.

Tipo de SQL Server

Formato de salida 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

Ejemplo

En el siguiente ejemplo se utiliza OPENXML con el tipo de datos 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

Vea también

Conceptos

Usar datos XML