1

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?

Shnugo
  • 66,100
  • 9
  • 53
  • 114
Neil Allen
  • 13
  • 3

2 Answers2

3

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

krish
  • 881
  • 7
  • 11
  • 1
    Excellent answer and very much appreciated. The link to the "What's New in FOR XML in Microsoft SQL Server 2005" explains the issue concisely. Although, rather embarrassingly, I didn't find fit when I searched for breaking changes in SQL Server 2014. – Neil Allen Jun 23 '16 at 19:49
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...

Shnugo
  • 66,100
  • 9
  • 53
  • 114
  • 1
    Thank you very much for your detailed answer and examples. Our application used SELECT ... FOR XML EXPLICIT in days of version .NET Framework 1.0 (or thereabouts) when the DataSet Class and ADO.NET didn't offer us much help with O/R Mapping. Since then we've watched and flirted with various versions the Entity Framework with interest but without it being sufficiently compelling to use it. However, with the move to SQL Server 2014 perhaps we'll find EF7 suitable. That said, I like XML although I feel a bit old fashioned saying that. I agree with your comment about SELECT .. FOR XML PATH('xyz'). – Neil Allen Jun 23 '16 at 20:21
  • 1
    Ultimately I was able to insert a crude shim and used Convert.FromBase64String() to get a byte array and BitConverter.ToUInt64() to get the data in same the form as the old SQL Server 2000 sourced XML – Neil Allen Jun 23 '16 at 20:25