아티클
04/01/2012

time, date, datetime2 및 datetimeoffset 데이터 형식을 포함한 XML 사용

기존 XML 스키마 문서 sqltypes.xsd에서는 FOR XML 및 HTTP/SOAP의 SQL Server 데이터 형식을 설명하는 데 사용되는 W3C XML 스키마 형식을 설명합니다.

sqltypes.xsd 스키마 문서

2004 XML 스키마 네임스페이스는 SQL Server 데이터 형식인 time, date, datetime2, 및datetimeoffset을 포함하도록 확장되었습니다.

.NET Framework System.Data.SqlDbType

SqlDbTypeEnum에 새롭게 추가된 열거형은 다음과 같습니다.

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

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

SQL Server 날짜 및 시간 데이터 형식에 대한 설명 및 매핑

다음 표에서는 SQL Server 2008에 새로 도입된 날짜 및 시간 데이터 형식에 대한 스키마 정의를 나열합니다.

데이터 형식	스키마 정의
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 스키마 네임스페이스 시스템 카탈로그

기존 2004 XML 스키마 네임스페이스에 새롭게 추가된 날짜 및 시간 형식 정보가 다음과 같은 XML 스키마 관련 시스템 카탈로그에 채워지며, 이러한 카탈로그에서 해당 정보를 쿼리할 수 있습니다.

Sys.xml_schemla_namespaces
Sys.xml_schema_collections
Sys.xml_schema_components

XML value() 메서드

XML value() 메서드를 사용하면 추출된 값의 SQL Server 데이터 형식을 나타낼 수 있습니다. 이는 지정된 형식에 대해 XSD 값이 SQL Server 값으로 변환됨을 의미합니다. 다음과 같은 캐스트에 대해 SQL Server 날짜 및 시간 형식이 지원됩니다.

날짜를 나타내는 XQuery 값 인스턴스는 date, datetime, smalldatetime, datetime2 또는 datetimeoffset을 비롯한 SQL Server의 모든 문자 형식으로 캐스팅될 수 있습니다.
시간을 나타내는 XQuery 값 인스턴스는 time, datetime, smalldatetime, datetime2 또는 datetimeoffset을 비롯한 SQL Server의 모든 문자 형식으로 캐스팅될 수 있습니다.
시간을 포함하지만 표준 시간대는 포함하지 않는 날짜를 나타내는 XQuery 값 인스턴스는 datetime, smalldatetime, datetimeoffet(0 표준 시간대 오프셋 포함), date(시간 부분 삭제), time(날짜 부분 삭제) 또는 datetime2를 비롯한 SQL Server의 모든 문자 형식으로 캐스팅될 수 있습니다.
Z 또는 표준 시간대 오프셋(+|-hh:mm)을 사용하는 시간 포함 날짜를 나타내는 XQuery 값 인스턴스는 datetime(표준 시간대 오프셋 없는 UTC), smalldatetime(표준 시간대 오프셋 없는 UTC), datetimeoffet, date(시간 부분 및 표준 시간대 오프셋 삭제), datetime2(표준 시간대 오프셋 없는 UTC) 또는 time(날짜 부분 및 표준 시간대 오프셋 삭제)을 비롯한 SQL Server의 모든 문자 형식으로 캐스팅될 수 있습니다.
캐스트로 인해 오버플로가 발생하거나 범위를 벗어나게 되면 오류 메시지가 반환됩니다.
날짜, 시간 또는 시간 값을 포함하는 날짜 값(표준 시간대 사용 유무에 상관없음)의 전체 자릿수(소수 자릿수 초)가 대상 형식의 전체 자릿수보다 많을수록 더 큰 소수 자릿수 초의 전체 자릿수가 반올림됩니다.
date 데이터 형식을 나타내는 XML 값 인스턴스의 날짜 형식은 SET LANGUAGE 및 SET DATEFORMAT 설정에 의해 결정됩니다.

예

다음 예에서는 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

XQuery 의사 함수

sql:column() 및 sql:variable()은 date, time, datetime2 및 datetimeoffset 데이터 형식을 인식합니다.

SQL Server 형식과 XSD 형식 간 매핑

sql:variable() 및 sql:column()의 경우 SQL Server 값에서 변환된 Xquery 값의 Xquery 기본 형식은 SQL Server 형식과 XSD 형식 간 매핑의 의미 체계에 의해 결정됩니다. 이러한 매핑 의미 체계는 확장된 XML 스키마 네임스페이스에 정의됩니다.

SQL Server 형식	XSD 형식
date	xsd:date
time	xsd:time
datetime2	xsd:datetime
datetimeoffset	xsd:datetime

XML 저장소 형식의 형식 매핑

SQL Server 2008에서 날짜 및 시간 형식의 XML 인스턴스에 대한 저장소 형식은 새로운 SQL Server 날짜 및 시간 형식을 사용함에 따라 변경됩니다. 다음 표에서는 XSD 형식과 SQL Server 데이터 형식 간 매핑을 보여 줍니다. 또한 이 매핑은 sql:column()과 sql:variable() 및 XML 날짜/시간 인스턴스 반환 결과 간의 작업 의미 체계를 결정합니다.

XSD 형식	TZ를 사용하는 XML 저장소 SQL Server 형식 매핑	TZ를 사용하지 않는 XML 저장소 SQL Server 형식 매핑
xs:date	datetimeoffset	date
xs:time	datetimeoffset	datetimeoffset
xs:dateTime	datetimeoffset	datetime2

FOR XML 및 XMLSCHEMA 절

SELECT 문에 FOR XML 또는 XMLSCHEMA 절이 지정된 경우 date, time, datetime2 및 datetimeoffset 데이터 형식에서 선언하는 SQL Server 열이 지원됩니다.

출력 형식

다음 표에서는 date, time, datetime2 및 datetimeoffset 데이터 형식에 대한 FOR XML 출력 형식을 나열합니다.

SQL Server 형식	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

예

다음 예에서는 FOR XML에 date, time, 및datetimeoffset 형식을 사용합니다.

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

XMLSCHEMA 절을 포함하는 XSD 인라인 스키마

XMLSCHEMA 절을 FOR XML 절과 함께 적용하는 경우 생성된 XSD 인라인 스키마는 확장된 기존 XML 스키마 네임스페이스에 있는 각각의 새 날짜 및 시간 형식에 대해 정의된 패턴 제한 메서드를 따릅니다.

OPENXML () 함수

다음 표에서는 date, time, datetime2 및 datetimeoffset 데이터 형식에 대한 OPENXML 입력 형식을 나열합니다.

SQL Server 형식	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

예

다음 예에서는 datetimeoffset 데이터 형식을 포함한 OPENXML을 사용합니다.

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

참고 항목

개념

XML 데이터 사용

다음을 통해 공유