Freigeben über

  • Artikel

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