Article
03/31/2012

Utilisation de XML avec les types de données time, date, datetime2 et datetimeoffset

Le document de schéma XML existant sqltypes.xsd décrit les types de schéma XML W3C utilisés pour décrire les types de données SQL Server dans FOR XML et HTTP/SOAP.

Document de schéma sqltypes.xsd

L'espace de noms de schéma XML 2004 a été étendu de façon à inclure les types de données SQL Server suivants :time, date, datetime2 etdatetimeoffset.

NET Framework System.Data.SqlDbType

Les énumérations suivantes sont ajoutées à SqlDbTypeEnum.

Type SQL Server	Type SqlDbType .NET Framework
date	Date
time	Time
datetime2	LargeDateTime
datetimeoffset	DateTimeWithTimeZone

Le SqlDbTypeEnum complet est le suivant :

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

Description et mappage des types de données Date et Time de SQL Server

Le tableau suivant répertorie les définitions de schéma pour les types de données de date et heure qui sont nouveaux dans SQL Server 2008.

Type de données	Définition de schéma
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>`

Catalogues système d'espace de noms de schéma XML

Les nouvelles informations de type de date et heure ajoutées dans l'espace de noms de schéma XML 2004 sont remplies et peuvent être interrogées à partir des catalogues système de schéma XML spécifiques suivants :

Sys.xml_schemla_namespaces
Sys.xml_schema_collections
Sys.xml_schema_components

Méthode XML value()

La méthode XML value() permet d'indiquer le type de données SQL Server de la valeur extraite. Cela suppose une conversion de la valeur XSD en valeur SQL Server pour le type spécifié. Les types de date et heure SQL Server sont pris en charge pour les conversions suivantes :

Toute instance de valeur XQuery qui représente une date peut être convertie en type date, datetime, smalldatetime, datetime2, datetimeoffset ou tout type de caractère dans SQL Server.
Toute instance de valeur XQuery qui représente une heure peut être convertie en type time, datetime, smalldatetime, datetime2, datetimeoffset ou tout type de caractère dans SQL Server.
Toute instance de valeur XQuery qui représente une date avec une heure et sans fuseau horaire peut être convertie en datetime, smalldatetime, datetimeoffet (avec 0 décalage de fuseau horaire), date (partie heure supprimée), time (partie date supprimée), datetime2, ou tout type de caractère dans SQL Server.
Toute instance de valeur XQuery qui représente une date avec une heure qui a un décalage Z ou de fuseau horaire (+|-hh:mm) peut être convertie en datetime (UTC sans décalage de fuseau horaire), smalldatetime (UTC sans décalage de fuseau horaire), datetimeoffet, date (partie heure et décalage de fuseau horaire supprimés), datetime2 (UTC sans décalage de fuseau horaire), time (partie date et décalage de fuseau horaire supprimés) ou tout type de caractère dans SQL Server.
Si la conversion entraîne un dépassement de capacité ou une condition hors limites, un message d'erreur est retourné.
Si une date, heure ou date avec valeur d'heure (avec ou sans fuseau horaire) a plus de précision (fractions de seconde) que le type cible, la précision de fraction de seconde supérieure est arrondie.
Le format de date de toute instance de valeur de XML qui représente un type de données date est déterminé par les paramètres SET LANGUAGE et SET DATEFORMAT.

Exemple

L'exemple suivant utilise la méthode 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

Pseudo-fonctions XQuery

sql:column() et sql:variable() reconnaissent les types de données date, time, datetime2 et datetimeoffset.

Mappage SQL Server vers type XSD

Pour sql:variable() et sql:column(), le type de base Xquery de la valeur Xquery transformée à partir de la valeur SQL Server est déterminé par la sémantique de mappage de SQL Server vers le type XSD. Cette sémantique de mappage est définie dans l'espace de noms de schéma XML étendu.

Type SQL Server	Type XSD
date	xsd:date
time	xsd:time
datetime2	xsd:datetime
datetimeoffset	xsd:datetime

Mappage du type de format de stockage XML

Dans SQL Server 2008, le format de stockage pour l'instance XML des types de date et heure est modifié en utilisant les nouveaux types de date et heure SQL Server. Le tableau ci-dessous illustre le mappage de type de données XSD vers SQL Server. Ce mappage détermine également la sémantique d'opération entre les résultats retournés par sql:column() et sql:variable() et les instances de date et heure XML.

Type XSD	Mappage de type SQL Server de stockage XML avec TZ	Mappage de type SQL Server de stockage XML sans TZ
xs:date	datetimeoffset	date
xs:time	datetimeoffset	datetimeoffset
xs:dateTime	datetimeoffset	datetime2

Clauses FOR XML et XMLSCHEMA

Les colonnes SQL Server déclarées par les types de données date, time, datetime2 et datetimeoffset sont prises en charge lorsque la clause FOR XML ou XMLSCHEMA est spécifiée dans l'instruction SELECT.

Format de sortie

Le tableau suivant répertorie les formats de sortie FOR XML pour les types de données date, time, datetime2 et datetimeoffset.

Type SQL Server	Format de sortie FOR XML
date	AAAA-MM-JJ
time	hh:mm:ss[.nnnnnnn]
datetime2	AAAA-MM-JJThh:mm:ss[.nnnnnnn]
datetimeoffset	AAAA-MM-JJThh:mm:ss[.nnnnnnn] [+\|-]hh:mm

Exemple

L'exemple ci-dessous utilise les types date, time et datetimeoffset avec 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"/>

Schéma inséré XSD avec la clause XMLSCHEMA

Lorsque la clause XMLSCHEMA est appliquée avec la clause FOR XML, le schéma inséré XSD généré respecte les méthodes de restriction de modèle définies pour chacun des nouveaux types de date et heure dans l'espace de noms de schéma XML existant étendu.

Fonction OPENXML ()

Le tableau suivant répertorie les formats d'entrée OPENXML pour les types de données date, time, datetime2 et datetimeoffset.

Type SQL Server	Format de sortie FOR XML
date	AAAA-MM-JJ
time	hh:mm:ss[.nnnnnnn]
datetime2	AAAA-MM-JJThh:mm:ss[.nnnnnnn]
datetimeoffset	AAAA-MM-JJThh:mm:ss[.nnnnnnn][+\|-]hh:mm

Exemple

L'exemple suivant utilise OPENXML avec le type de données 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

Voir aussi

Concepts

Utilisation de données XML

Partager via

Utilisation de XML avec les types de données time, date, datetime2 et datetimeoffset

Document de schéma sqltypes.xsd

NET Framework System.Data.SqlDbType

Description et mappage des types de données Date et Time de SQL Server

Catalogues système d'espace de noms de schéma XML

Méthode XML value()

Exemple

Pseudo-fonctions XQuery

Mappage SQL Server vers type XSD

Mappage du type de format de stockage XML

Clauses FOR XML et XMLSCHEMA

Format de sortie

Exemple

Schéma inséré XSD avec la clause XMLSCHEMA

Fonction OPENXML ()

Exemple

Voir aussi

Concepts

Ressources supplémentaires