Search code examples
sql-servert-sqlvarbinary

SQL string to varbinary through XML different than nvarchar

We currently have a function in SQL which I simply do not understand.

Currently we convert a nvarchar to XML, and then select the XML value, and convert that to a varbinary.

When I try to simplify this to convert the nvarchar directly to varbinary, the output is different... Why?

--- Current situation:
Declare @inputString nvarchar(max) = '4d95605d1b8f3bca5ea3e0d2af26027004d17218152e726da0622d669a71f85c'

--1: input to XML
declare @inputXML XML = convert(varchar(max), @inputString)

--2: input XML to binary
declare @inputBinray varbinary(max) = @inputXML.value('(/)[1]', 'varbinary(max)')

select @inputString -- 4d95605d1b8f3bca5ea3e0d2af26027004d17218152e726da0622d669a71f85c
select @inputXML -- 4d95605d1b8f3bca5ea3e0d2af26027004d17218152e726da0622d669a71f85c
select @inputBinray -- 0xE1DF79EB4E5DD5BF1FDDB71AE5E6B77B477669FDBAD36EF4D38775EF6D7CD79D9EEF6E9D6B4EB6D9DEBAF5AEF57FCE5C

--- New situation
--1: Input to binary
declare @inputString2 varbinary(max) = CAST(@inputString as varbinary(max));

select @inputString2 -- 0x3400640039003500360030003500640031006200380066003300620063006100350065006100330065003000640032006100660032003600300032003700300030003400640031003700320031003800310035003200650037003200360064006100300036003200320064003600360039006100370031006600380035006300
Solution

  • Using the value() function to get a XML value specified as varbinary(max) will read the data as if it was Base64 encoded. Casting a string to varbinary(max) does not, it treats it as just any string.

    If you use the input string QQA= which is the letter A in UTF-16 LE encoded to Base64 you will see more clearly what is happening.

    XML gives you 0x4100, the varbinary of the letter A, and direct cast on the string gives you 0x5100510041003D00 where you have two 5100 = "Q" and of course one 4100 = "A" followed by a 3D00 = "="