Artikel
03/30/2012

Verwenden von XML mit den Datentypen 'time', 'date', 'datetime2' und 'datetimeoffset'

Das vorhandene XML-Schemadokument sqltypes.xsd enthält eine Beschreibung der W3C XML-Schematypen, die zur Beschreibung der SQL Server-Datentypen in FOR XML und HTTP/SOAP verwendet werden.

sqltypes.xsd-Schemadokument

Der XML-Schemanamespace von 2004 wurde um die folgenden SQL Server-Datentypen erweitert:time, date, datetime2 unddatetimeoffset.

NET Framework System.Data.SqlDbType

Die folgenden Enumerationen wurden SqlDbTypeEnum hinzugefügt.

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

Dies ist die vollständige 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>

Beschreibung und Zuordnung von Datums- und Zeitdatentypen in SQL Server

Die folgende Tabelle enthält die Schemadefinitionen für die in SQL Server 2008 neuen Datums- und Zeitdatentypen.

Datentyp	Schemadefinition
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-Schemanamespace-Systemkataloge

Die dem vorhandenen XML-Schemanamespace von 2004 hinzugefügten Datums- und Zeitdatentypen können aus den folgenden schemaspezifischen XML-Systemkatalogen abgerufen werden:

Sys.xml_schemla_namespaces
Sys.xml_schema_collections
Sys.xml_schema_components

XML value()-Methode

Die XML value()-Methode bietet eine Möglichkeit, den SQL Server-Datentyp des extrahierten Werts anzugeben. Dies umfasst die Konvertierung des XSD-Werts in einen SQL Server-Wert für den angegebenen Typ. Die Datums- und Zeitdatentypen von SQL Server unterstützen folgende Umwandlungen:

Jeder beliebige XQuery-Wert, der ein Datum darstellt, kann in date, datetime, smalldatetime, datetime2, datetimeoffset und in jeden Zeichentyp in SQL Server umgewandelt werden.
Jeder beliebige XQuery-Wert, der einen Zeitwert darstellt, kann in time, datetime, smalldatetime, datetime2, datetimeoffset und in jeden Zeichentyp in SQL Server umgewandelt werden.
Jeder beliebige XQuery-Wert, der ein Datum mit Zeit und ohne Zeitzone darstellt, kann in datetime, smalldatetime, datetimeoffet (mit Zeitzonenoffset 0), date (time-Teil wird entfernt), time (date-Teil wird entfernt), datetime2, und in jeden Zeichentyp in SQL Server umgewandelt werden.
Jeder beliebige XQuery-Wert, der ein Datum mit Zeit und Zeitzone oder Zeitzonenoffset (+|-hh:mm) darstellt, kann in datetime (UTC ohne Offset), smalldatetime (UTC ohne Offset), datetimeoffet, date (ohne time-Teil und Offset), datetime2 (UTC ohne Offset), time (ohne date-Teil und Offset) und in jeden Zeichentyp in SQL Server umgewandelt werden.
Wenn die Umwandlung einen Überlauf oder einen Gültigkeitsfehler zur Folge hat, wird eine Fehlermeldung zurückgegeben.
Wenn der date-, time- oder der datetime-Wert (mit oder ohne Zeitzone) eine höhere Genauigkeit (Sekundenbruchteile) aufweist als der Zieltyp, wird er gerundet.
Das Datumsformat für XML-Werte zur Darstellung eines date-Datentyps wird durch die SET LANGUAGE-Einstellung und die SET DATEFORMAT-Einstellung festgelegt.

Beispiel

Im folgenden Beispiel wird die value()-Methode verwendet.

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

sql:column() und sql:variable() erkennen die Datentypen date, time, datetime2 und datetimeoffset.

Zuordnung von SQL Server zu XSD

Für sql:variable() und sql:column() wird der Xquery-Basistyp des Xquery-Werts, der aus dem SQL Server-Wert umgewandelt wird, durch die Semantik der SQL Server-zu-XSD-Zuordnung bestimmt. Diese Zuordnungssemantik ist im erweiterten XML-Schemanamespace definiert.

SQL Server-Datentyp	XSD-Datentyp
date	xsd:date
time	xsd:time
datetime2	xsd:datetime
datetimeoffset	xsd:datetime

XML-Speicherformattyp-Zuordnung

In SQL Server 2008 ändert sich das Speicherformat für die XML-Instanz von Datums- und Zeitdatentypen aufgrund der Verwendung der neuen Datums- und Zeitdatentypen von SQL Server. Die folgende Tabelle enthält die Zuordnung von XSD- zu SQL Server-Datentypen. Diese Zuordnung bestimmt außerdem die Vorgangssemantik zwischen den Rückgabeergebnissen von sql:column() und sql:variable() sowie XML-Datums- und Zeitinstanzen.

XSD-Datentyp	XML-Speicher-/SQL Server-Typzuordnung mit Zeitzone	XML-Speicher-/SQL Server-Typzuordnung ohne Zeitzone
xs:date	datetimeoffset	date
xs:time	datetimeoffset	datetimeoffset
xs:dateTime	datetimeoffset	datetime2

FOR XML-Klausel und XMLSCHEMA-Klausel

SQL Server-Spalten, die mit den Datentypen date, time, datetime2 und datetimeoffset deklariert werden, werden unterstützt, wenn in der SELECT-Anweisung die FOR XML-Klausel oder die XMLSCHEMA-Klausel angegeben ist.

Ausgabeformat

Die folgende Tabelle enthält die FOR XML-Ausgabeformate für die Datentypen date, time, datetime2 und datetimeoffset.

SQL Server-Datentyp	FOR XML-Ausgabeformat
date	YYYY-MM-DD
time	hh:mm:ss[.nnnnnnn]
datetime2	YYYY-MM-DDThh:mm:ss[.nnnnnnn]
datetimeoffset	YYYY-MM-DDThh:mm:ss[.nnnnnnn] [+\|-]hh:mm

Beispiel

Im folgenden Beispiel werden die Datentypen date, time unddatetimeoffset mit FOR XML verwendet.

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-Inlineschema mit der XMLSCHEMA-Klausel

Bei Anwendung der XMLSCHEMA-Klausel mit der FOR XML-Klausel gelten für das generierte XSD-Inlineschema die Musterbeschränkungsmethoden, die für die einzelnen neuen Datums- und Zeitdatentypen im erweiterten vorhandenen XML-Schemanamespace definiert sind.

OPENXML ()-Funktion

Die folgende Tabelle enthält die OPENXML-Eingabeformate für die Datentypen date, time, datetime2 und datetimeoffset.

SQL Server-Datentyp	FOR XML-Ausgabeformat
date	YYYY-MM-DD
time	hh:mm:ss[.nnnnnnn]
datetime2	YYYY-MM-DDThh:mm:ss[.nnnnnnn]
datetimeoffset	YYYY-MM-DDThh:mm:ss[.nnnnnnn][+\|-]hh:mm

Beispiel

Im folgenden Beispiel wird OPENXML mit dem datetimeoffset-Datentyp verwendet.

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

Siehe auch

Konzepte

Verwenden von XML-Daten

Freigeben über

Verwenden von XML mit den Datentypen 'time', 'date', 'datetime2' und 'datetimeoffset'

sqltypes.xsd-Schemadokument

NET Framework System.Data.SqlDbType

Beschreibung und Zuordnung von Datums- und Zeitdatentypen in SQL Server

XML-Schemanamespace-Systemkataloge

XML value()-Methode

Beispiel

XQuery-Pseudofunktionen

Zuordnung von SQL Server zu XSD

XML-Speicherformattyp-Zuordnung

FOR XML-Klausel und XMLSCHEMA-Klausel

Ausgabeformat

Beispiel

XSD-Inlineschema mit der XMLSCHEMA-Klausel

OPENXML ()-Funktion

Beispiel

Siehe auch

Konzepte

Zusätzliche Ressourcen