Selecting VarBinary data as Base64

Disclaimer: This post is more for me to remember how I did this for future me to find when I Google it :). But if it helps someone else, awesome.

I was recently working on a feature that required storing a small image (PNG) in a database. We're using SQL Server, so I used the VarBinary type.

Once the image was stored, I want to just check that it was stored correctly as there was a bit of a process involved in generating and storing it.

I did a SELECT on the table and saw something like this: 0x89504E470D0A1A0A0000000D49... which I assume is HEX of some sort, but when I tried to use an online tool to quickly convert the HEX back to PNG I got all sorts of errors.

After a bit of Googling, I found a way to select the data as Base64:

select ImageData, base64  
from MyTableWithImagesInIt  
cross apply (select ImageData '*' for xml path('')) T (base64)  

I was then able to paste this Base64 string into this helpful tool and get a quick preview of the PNG and to confirm that everything was generated and saved correctly.