Converting XML Datetime to SQL Datetime

SQL Server XML

Gary8877


I have an XML colmn in SQL 2005 table which looks like:

<abc>

<abcdate>2007-01-31T13:47:27.25-05:00</abcdate>

</abc>

The following query :

SELECT xmlColumn.value('(/abc/abcDate)[1]', 'nvarchar(30)') FROM abcTABLE

Returns 2007-01-31T13:47:27.25-05:00

---------

SELECT CAST(xmlColumn.value('(/abc/abcDate)[1]', 'nvarchar(30)') AS DATETIME) FROM abcTABLE

Returns

Msg 241, Level 16, State 1, Line 1

Conversion failed when converting datetime from character string.

-----------------

SELECT CAST(xmlColumn.value('(/abc/abcDate)[1]', 'nvarchar(19)') AS DATETIME) FROM abcTABLE

Returns 2007-01-31 13:47:27.000

because length of 19 trims the milliseconds

------------------

Is it possible to convert this type of XML data type and still acheive accuracy to the milliseconds

Thanks

Gary


Top

Re: Converting XML Datetime to SQL Datetime

Konstantin Kosinsky


You could use convert function with 126/127 style (for more info http://msdn2.microsoft.com/en-us/library/ms187928.aspx):

declare @t1 varchar(25)

declare @t2 varchar(25)

set @t1 = '2007-01-31T13:47:27.25-05:00'

set @t2 = '2006-12-12T23:45:12.10'

select convert(datetime,@t2,126)

select convert(datetime,@t1,127) --It must works, but don't work on my PC. I think something wrong with my system





Top

Re: Converting XML Datetime to SQL Datetime

Gary8877

Hi Konstantin Kosinsky

It is not working at my machine as well, the "-" is the culprit still.

Style 127 is for ISO8601 with time zone Z: yyyy-mm-ddThh:mm:ss.mmmZ

It does not like the hyphen "-"






Top

Re: Converting XML Datetime to SQL Datetime

Konstantin Kosinsky

Just quote from BOL:

The optional time zone indicator, Z, is used to make it easier to map XML datetime values that have time zone information to SQL Server datetime values that have no time zone. Z is the indicator for time zone UTC-0. Other time zones are indicated with HH:MM offset in the + or - direction. For example: 2006-12-12T23:45:12-08:00.

But it does not work. :).

At this moment i could propose substring by last "-" and use 126 or 127 style


Top

Re: Converting XML Datetime to SQL Datetime

Galex Yen MSFT

The docs are misleading here. If you look carefully at the table above the quote, style 127 only work with Zulu timezone ("Z"). The workaround here is to use XQuery simple type construction.

declare @t1 xml

set @t1 = '<abc>2007-01-31T13:47:27.25+05:00</abc>'

select @t1.value('xs:dateTime(/abc[1])', 'datetime')

-galex


Top

Re: Converting XML Datetime to SQL Datetime

Gary8877

Hi Galex

I tried:

SELECT

Assessmentxml.value('xs:dateTime(/Abc/AbcDate)[1]', 'datetime') AS [AssessmentExpectedStartDate]

FROM dbo.Assessment

I got:

Msg 2365, Level 16, State 1, Line 10

XQuery [dbo.Assessment.AssessmentXML.value()]: Cannot explicitly convert from 'xdt:untypedAtomic *' to 'xs:dateTime'

Then I tried:

SELECT

Assessmentxml.value('(/Abc/AbcDate)[1] CAST AS xs:dateTime', 'datetime') AS [AssessmentExpectedStartDate]

FROM dbo.Assessment

and got

Msg 2370, Level 16, State 1, Line 2

XQuery [dbo.Assessment.AssessmentXML.value()]: No more tokens expected at the end of the XQuery expression. Found 'CAST'.

What is the correct syntax to make it work

Thanks



Top

Re: Converting XML Datetime to SQL Datetime

Galex Yen MSFT

In SQL Server, the expression you are casting (construction is the same) requires a singleton. Since you are using untyped XML, you need to use a positional predicate.

Please try:

SELECT

Assessmentxml.value('xs:dateTime(/Abc/AbcDate[1])', 'datetime') AS [AssessmentExpectedStartDate]

FROM dbo.Assessment

Notice the [1] predicate is inside the construction.

Also, XQuery is case sensitive, so you should be using "cast as" instead of "CAST AS".

-galex


Top

Re: Converting XML Datetime to SQL Datetime

Gary8877

Hi Galex

I get the same error. Please try this:

set ansi_nulls, quoted_identifier, ansi_warnings, ansi_padding ON

declare @Ax table

( AxID uniqueidentifier not null default(newid())

, AxRefDateTag sysname

, AxXML as cast(

'<Assessment xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

<AssessmentID>' + cast(AxID as sysname) + '</AssessmentID>'

+ AxRefDateTag + '</Assessment>' as xml)

)

insert @Ax(AxRefDateTag) values('<AssessmentReferenceDate>2007-01-31T13:47:27.25+05:00</AssessmentReferenceDate>')

SELECT

AxRefDateTag

,AxXml.value('xs:dateTime(/Assessment/AssessmentReferenceDate[1])', 'datetime') AS [AssessmentExpectedStartDate]

FROM @Ax



Top

Re: Converting XML Datetime to SQL Datetime

Galex Yen MSFT

Gary,

That was my bad. I forgot to put keep the () around the path expression.

xs:dateTime((/Assessment/AssessmentReferenceDate)[1])

Keep in mind, this is also another option, but has different semantics:

xs:dateTime(/Assessment[1]/AssessmentReferenceDate[1])

Sorry for the confusion.

Regards,

Galex


Top

Re: Converting XML Datetime to SQL Datetime

Gary8877

This works well :) - thanks a lot Galex



Top

Re: Converting XML Datetime to SQL Datetime

Ramasubramanian

Hi Gary

I am also getting the same issue.

Actually straightforward I cannot use the one which you sent ( 'xsBig SmileateTime(/Assessment/AssessmentReferenceDate[1])', 'datetime')

I cannot say [1], i have to use the variable instead of that, because I am looping through the data.

I am using the below code. It is working fine when the date time is in this format 2007-01-31T13:47:27.25' only.

CAST(CAST(Message.query('data(//SHIPMENT/ISSUE_DT)[sql:variable("@vcounter")]') AS VARCHAR) AS DATETIME) END,

I tried like this with the format you had given

Message.value('xsBig SmileateTime((//SHIPMENT/ISSUE_DT)[sql:variable("@vcounter")])', 'datetime'))

but i am getting an error while compiling

XQuery [Ediinbound.Message.value()]: Cannot explicitly convert from 'xdt:untypedAtomic *' to 'xsBig SmileateTime'.

Kindly help me ASAP...

Thanks

Ram


Top

Re: Converting XML Datetime to SQL Datetime

Gary8877

This is how it worked. Galex showed me how to put brackets around the XPATH.

set ansi_nulls, quoted_identifier, ansi_warnings, ansi_padding ON

declare @Ax table

( AxID uniqueidentifier not null default(newid())

, AxRefDateTag sysname

, AxXML as cast(

'<Assessment xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

<AssessmentID>' + cast(AxID as sysname) + '</AssessmentID>'

+ AxRefDateTag + '</Assessment>' as xml)

)

insert @Ax(AxRefDateTag) values('<AssessmentReferenceDate>2007-01-31T13:47:27.25+05:00</AssessmentReferenceDate>')

SELECT * FROM @Ax

SELECT

AxRefDateTag

,AxXml.value('xsBig SmileateTime((/Assessment/AssessmentReferenceDate)[1])', 'datetime') AS [AssessmentExpectedStartDate]

FROM @Ax

I haven't tried the variable before, but I think from the above explanation [1] or singleton is a MUST in SQL Server queries.





Top