• Articolo

Utilizzo del codice XML con i tipi di dati time, date, datetime2 e datetimeoffset

Nel documento di schema XML esistente sqltypes.xsd vengono illustrati i tipi di schema XML W3C utilizzati per descrivere i tipi di dati di SQL Server in FOR XML e HTTP/SOAP.

Documento dello schema sqltypes.xsd

Lo spazio dei nomi dello schema XML del 2004 è stato esteso per includere i tipi di dati di SQL Server, ovvero time, date, datetime2 e datetimeoffset.

System.Data.SqlDbType .NET Framework

A SqlDbTypeEnum sono state aggiunte le enumerazioni seguenti:

Tipo SQL Server

.SqlDbType .NET Framework

date

Date

time

Time

datetime2

LargeDateTime

datetimeoffset

DateTimeWithTimeZone

L'enumerazione SqlDbTypeEnum completa è la seguente:

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

Descrizione e mapping dei tipi di dati date e time di SQL Server

Nella tabella seguente vengono elencate le definizioni dello schema per i tipi di dati date e time introdotti in SQL Server 2008.

Tipo di dati

Definizione dello schema

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>

Cataloghi di sistema dello spazio dei nomi dello schema XML

Le informazioni sui nuovi tipi time e date aggiunti allo spazio dei nomi dello schema XML del 2004 esistente vengono archiviate e possono essere oggetto di query dai seguenti cataloghi di sistema specifici dello schema XML:

  • Sys.xml_schemla_namespaces

  • Sys.xml_schema_collections

  • Sys.xml_schema_components

Metodo value() XML

Il metodo value() XML consente di indicare il tipo di dati di SQL Server del valore estratto. Questo implica una conversione dal valore XSD a un valore di SQL Server per il tipo specificato. I tipi date e time di SQL Server sono supportati per l'esecuzione dei seguenti cast:

  • È possibile eseguire il cast di qualsiasi istanza del valore XQuery che rappresenta una data in un tipo di dati date, datetime, smalldatetime, datetime2, datetimeoffset oppure in qualsiasi tipo di carattere di SQL Server.

  • È possibile eseguire il cast di qualsiasi istanza del valore XQuery che rappresenta un'ora in un tipo di dati time, datetime, smalldatetime, datetime2, datetimeoffset oppure in qualsiasi tipo di carattere di SQL Server.

  • È possibile eseguire il cast di qualsiasi istanza del valore XQuery che rappresenta una data con un'ora senza fuso orario in un tipo di dati datetime, smalldatetime, datetimeoffet (con differenza di fuso orario pari a 0), date (parte relativa all'ora eliminata), time (parte relativa alla data eliminata), datetime2, oppure in qualsiasi tipo di carattere di SQL Server.

  • È possibile eseguire il cast di qualsiasi istanza del valore XQuery che rappresenta una data con un'ora con indicatore Z o differenza di fuso orario (+|-hh:mm) in un tipo di dati datetime (ora UTC senza differenza di fuso orario), smalldatetime (ora UTC senza differenza di fuso orario), datetimeoffet, date (parte relativa all'ora e differenza di fuso orario eliminate), datetime2 (ora UTC senza differenza di fuso orario), time (parte relativa alla data e differenza di fuso orario eliminate) oppure in qualsiasi tipo di carattere di SQL Server.

  • Se l'esecuzione del cast provoca un overflow o una condizione di valori non compresi nell'intervallo valido, verrà restituito un messaggio di errore.

  • Se una data, un'ora o una data con il valore dell'ora (con o senza fuso orario) è caratterizzata da maggiore precisione (secondi frazionari) rispetto al tipo di destinazione, la precisione più elevata determinata dai secondi frazionari verrà arrotondata.

  • Il formato della data di qualsiasi istanza del valore XML che rappresenta un tipo di dati date viene determinato dalle impostazioni SET LANGUAGE e SET DATEFORMAT.

Esempio

Nell'esempio seguente viene utilizzato il metodo 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

Pseudofunzioni XQuery

sql:column() e sql:variable() riconoscono i tipi di dati date, time, datetime2 e datetimeoffset.

Mapping dei tipi SQL Server ai tipi XSD

Per sql:variable() e sql:column(), il tipo di base Xquery del valore Xquery trasformato dal valore di SQL Server viene determinato dalla semantica del mapping dal tipo SQL Server al tipo XSD. Tale semantica viene definita nello spazio dei nomi dello schema XML esteso.

Tipo SQL Server

Tipo XSD

date

xsd:date

time

xsd:time

datetime2

xsd:datetime

datetimeoffset

xsd:datetime

Mapping dei tipi del formato di archiviazione XML

In SQL Server 2008 il formato di archiviazione per l'istanza XML dei tipi date e time è stato modificato mediante l'utilizzo dei nuovi tipi date e time di SQL Server. Nella tabella seguente viene illustrato il mapping tra i tipi di dati XSD e SQL Server. Tale mapping determinerà anche la semantica dell'operazione tra i risultati restituiti da sql:column() e sql:variable() e le istanze relative ai tipi date e time XML.

Tipo XSD

Mapping dei tipi SQL Server del formato di archiviazione con fuso orario

Mapping dei tipi SQL Server del formato di archiviazione senza fuso orario

xs:date

datetimeoffset

date

xs:time

datetimeoffset

datetimeoffset

xs:dateTime

datetimeoffset

datetime2

Clausole FOR XML e XMLSCHEMA

Quando la clausola FOR XML o XMLSCHEMA è specificata nell'istruzione SELECT, sono supportate colonne di SQL Server dichiarate dai tipi di dati date, time, datetime2 e datetimeoffset.

Formato di output

Nella tabella seguente vengono elencati i formati di output FOR XML per i tipi di dati date, time, datetime2 e datetimeoffset.

Tipo SQL Server

Formato di output FOR XML

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

Esempio

Nell'esempio seguente vengono utilizzati i tipi date, time e 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"/>

Schema inline XSD con la clausola XMLSCHEMA

Quando la clausola XMLSCHEMA viene applicata con la clausola FOR XML, lo schema inline XSD generato segue i metodi di restrizione dello schema definiti per ognuno dei nuovi tipi date e time nello spazio dei nomi dello schema XML esteso esistente.

Funzione OPENXML ()

Nella tabella seguente vengono elencati i formati di input OPENXML per i tipi di dati date, time, datetime2 e datetimeoffset.

Tipo SQL Server

Formato di output FOR XML

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

Esempio

Nell'esempio seguente viene utilizzato OPENXML con il tipo di dati 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

Vedere anche

Concetti

Utilizzo di dati XML