Converting timestamp to xml

It is possible to do the following:

SELECT SomeTimestampColumn FROM SomeTable FOR XML PATH('')

and the result looks like a string e.g.

<SomeTimestampColumn >AAAAAATJ2y4=</SomeTimestampColumn>

But for some reason, SQL doesn't allow me to do this

SELECT CONVERT(xml,SomeTimestampColumn) FROM SomeTable

There are two related quest开发者_运维技巧ions here:

1. Is there some other way (besides XML PATH('')) to get the equivalent string from sql or
2. Is there a better way of capturing/converting that timestamp into a more manipulable string (I need to pass this back and forth between a c# application and will be using it to determine that no-one else has altered the row in the meantime)

Thanks, Mike

declare @T table(SomeTimestampColumn timestamp, id int)

insert into @T(id) values(1)
insert into @T(id) values(2)

select 
  (select SomeTimestampColumn for xml path('')) as SomeTimestampColumn
from @T

If you want the column to be XML type

select 
  (select SomeTimestampColumn for xml path(''), type) as SomeTimestampColumn
from @T

Or if you only want the value

select
  T1.ID,
  T3.N.value('.', 'varchar(50)') as SomeTimestampColumn
from @T as T1
  cross apply
    (select T1.SomeTimestampColumn for xml path(''), type) as T2(X)
  cross apply
    T2.X.nodes('SomeTimestampColumn') as T3(N)

Result

ID          SomeTimestampColumn
----------- --------------------------------------------------
1           AAAAAAACDZg=
2           AAAAAAACDZk=

The conversion is to Base64 and you can convert it back. Here is a link to some UDF's that can do the conversion as well. http://www.vbforums.com/showthread.php?t=554886

declare @T table(SomeTimestampColumn timestamp, id int)

insert into @T(id) values(1)

declare @Base64 varchar(max)
declare @Binary varbinary(max)

select
  @Binary = T1.SomeTimestampColumn,
  @Base64 = T3.N.value('.', 'varchar(50)')
from @T as T1
  cross apply
    (select T1.SomeTimestampColumn for xml path(''), type) as T2(X)
  cross apply
    T2.X.nodes('SomeTimestampColumn') as T3(N)

select
  @Base64,
  CAST(N'' AS XML).value('xs:base64Binary(xs:hexBinary(sql:variable("@Binary")))', 'VARCHAR(MAX)'),
  @Binary,
  CAST(N'' AS XML).value('xs:base64Binary(sql:variable("@Base64"))', 'VARBINARY(MAX)')

Result:

AAAAAAAAJ3k=    AAAAAAAAJ3k=    0x0000000000002779  0x0000000000002779

This is what you want to do:

SELECT convert(datetime, SomeTimestampColumn) 
FROM SomeTable FOR XML PATH('SomeTimestampColumn')