CODEWITHSUNDEEP
sql-servert-sqlvarbinary
Bjorn
Bjorn

Reputation: 78

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

Upvotes: 1

Views: 924

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67341

Might be I get something wrong, but - if I understand you correctly - I think you simply want to get a real binary from a HEX-string, which just looks like a binary. Correct?

Above I wrote "simply", but this was not simple at all a while ago.

I'm not sure at the moment, but I think it was version v2012, which enhanced CONVERT() (read about binary values and how the third parameter works) and try this:

DECLARE @hexString VARCHAR(max)='4d95605d1b8f3bca5ea3e0d2af26027004d17218152e726da0622d669a71f85c';

SELECT CONVERT(varbinary(max),@hexString,2);

The result is a real binary

0x4D95605D1B8F3BCA5EA3E0D2AF26027004D17218152E726DA0622D669A71F85C

What might be the reason for your issue:

Very long ago, I think it was until v2005, the default encoding of varbinaries in XML was a HEX string. Later this was changed to base64. Might be, that you code was used in a very old environment and was upgraded to a higher version?

Today we use XML in a smiliar way to create and to read base64, which is not supported otherwise. Maybe your code did something similar with HEX strings...?

One more hint for this: The many 00 in your New Situation example show clearly, that this is a two-byte encoded NVARCHAR string. Contrary, your Current Situation shows a simple HEX string.

Your final result is just the binary pattern of your input as string:

Upvotes: 1

Mikael Eriksson
Mikael Eriksson

Reputation: 139010

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 = "="

Upvotes: 1

Related Questions