As a person who messes with SQL Server there is a pretty strong probability that you are using SQL Server Management Studio (SSMS). It is a very full featured tool, but does have a couple of problems and quirks (like crashing on large result sets due to it reaching the maximum memory allocation for a 32bit process).

The other day I came across another quirk, maybe even a bug, which can happen when returning and working with XML results, which could lead you to copy incorrect data…

Let’s take a look at the problem using a very simple example.

Here we are going to return a single row of data, with three columns, one of which is XML.

SELECT
Col1 = 'These are not the results you are looking for' ,
Col2 = CAST('This is what you really expected.' AS XML) ,
Col3 = 'You were not looking for this either.'

From here you can click on the XML text to view the data (while not a big deal for this, when reviewing large sets of data it can be really important, or for copying and pasting for something else)

Now, using one of a little known feature of SSMS we can click and drag on the header of  the Col2 column and drag it over to be the first column. It still shows the correct result data.

The problem comes if we click on the XML again to show it

As you can see we are not returning the correct information any longer. We now show the results from Col1, where we expect Col2. If you wanted to take this a step further try reordering the columns again. You’ll find whichever position the XML column is in, it will return the original contents of that column rather than the actual visible content.

I’ve been able to reproduce this with SSMS 2012, 2014, and the 2016 CTPs.

While this isn’t a huge deal, it could cause you problems if you return multiple XML columns with different results, and like to reorder things in SSMS. So just be careful when clicking on things out there, the results might not be what you expect.