Different values returned by SQL Server 2000 and SQL Server 2014 when returning results using for xml explicit

Question

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?

Answer 1

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

Answer 2

Timestamp values in SQL Server 2005 is serialized as base64 encoded binary values instead of number representation in SQL server 2000,you can refer this link for all the changes made in for XML in SQL server 2005