We have converted a database from SQL Server 2000 to SQL Server 2014. We require XML output and use T-SQL's for xml explicit option to return XML.
The timestamp value has been preserved during the conversion. i.e. A conventional query returns the timestamp value 0x00000000057A12B8 on both servers.
When using for xml explicit the SQL Server 2014 output is <Ts>AAAAAAV6Erg=</Ts> and the SQL Sever 2000 output is <Ts>91886264</Ts>
Can anybody explain the difference?
First of all: Congratulations! You will be happy without SQL Server 2000!
Second: SELECT ... FOR XML EXPLICIT is outdated and should be (in almost all cases) replaced with calls of SELECT ... FOR XML PATH('xyz'). This gives you much better control and a very intuitive approach.
Now to your question:
As you surely now, TIMESTAMP is - other than ANSI-SQL, where it is the same a T-SQL's DATETIME - a binary type, better used with the synonym ROWVERSION to avoid confusions.
SQL Server 2014 will implicitly convert binary data to base64 to let you include this within an XML (text based).
Try this:
DECLARE @varBin VARBINARY(MAX)=0x00000000057A12B8;
DECLARE @asXML XML= (SELECT @varBin FOR XML PATH('xyz'));
SELECT @asXML;
--result: <xyz>AAAAAAV6Erg=</xyz>
--re-read the value
SELECT @asXML.value('/xyz[1]','varbinary(max)')
--result: 0x00000000057A12B8
The numeric value you've got is just the decimal number to express the same value:
HEX 57A12B8
DEC 91.886.264
BIN 0101 0111 1010 0001 0010 1011 1000
With this query
SELECT CAST(@varBin AS BIGINT)
you would get the number - if you need it...