how to read varbinary data in xml in sql server

Question

i've data in XML like

<Values>
  <Id>7f8a5d20-d171-42f5-a222-a01b5186a048</Id>
  <DealAttachment>
    <AttachmentId>deefff3f-f63e-4b4c-8e76-68b6db476628</AttachmentId>
    <IsNew>true</IsNew>
    <IsDeleted>false</IsDeleted>
    <DealId>7f8a5d20-d171-42f5-a222-a01b5186a048</DealId>
    <AttachmentName>changes2</AttachmentName>
    <AttachmentFile>991049711010310132116104101321011099710510832115117981061019911645100111110101131011711210097116101329811110012132116101120116321151171031031011151161011003298121326610510810845100111110101131010011132110111116321151011101003210110997105108321051103299971151013211110232117112100971161011003299108105101110116115329811711632115101110100321051103299971151013211110232100101108101116101100329910810510111011611545321001111101011310991141019711610132973211510111297114971161013211697981081013211611132991111101169710511032117115101114115321161113211910411110932101109971051083211910510810832981013211510111011645100111110101131011611411711099971161013211610410132108111103321169798108101329710211610111432111110101321091111101161044510011111010113108411497110115108971161051111101154532100111110101131097100100321129711497109101116101114321161113210611198321161113211411711032115105108101110116108121451001011089712110110013101310131013101310131010511010211111410932981051081083210511032999711510132981111161043211711210097116105111110471001011081011161051111104432117112100971161051111103211910510810832110111116329810132105110102111114109101100</AttachmentFile>
    <AttachmentType>.txt</AttachmentType>
  </DealAttachment>
</Values>

where AttachmentFile is varbinary(max)

DECLARE @AttachmentId uniqueidentifier, 
    @DealId uniqueidentifier, 
    @IsNew bit,
    @IsDeleted bit,
    @AttachmentName varchar(100), 

    @AttachmentFile varbinary(max), 
    @AttachmentType varchar(50) 

SET @DealId = @SubmitXml.value('(Values/Id/node())[1]', 'uniqueidentifier')
    SET @AttachmentId = @SubmitXml.value('(Values/DealAttachment/AttachmentId/node())[1]', 'uniqueidentifier')
    SET @IsNew = @SubmitXml.value('(Values/DealAttachment/IsNew/node())[1]', 'bit')
    SET @IsDeleted = @SubmitXml.value('(Values/DealAttachment/IsDeleted/node())[1]', 'bit')
    SET @AttachmentName = @SubmitXml.value('(Values/DealAttachment/AttachmentName/node())[1]', 'varchar(100)')
    SET @AttachmentFile = @SubmitXml.value('(Values/DealAttachment/AttachmentFile/node())[1]', 'varbinary(max)')
    SET @AttachmentType = @SubmitXml.value('(Values/DealAttachment/AttachmentType/node())[1]', 'varchar(50)')

But, after above statement @AttachmentFile is NULL or blankspace.

How was `AttachementFile` encoded? To my (relatively ignorant) eye, your example doesn't look like a format which will natively cast to a `varbinary` data type; you might have to extract it from XML as a string and do some more work to decode it correctly. — Ed Harper, Apr 08 '16 at 14:53
For encoding I've used exact same code as here http://www.aspsnippets.com/Articles/Upload-and-Download-files-from-SQL-Server-Database-in-ASPNet.aspx — donstack, Apr 08 '16 at 14:57
I tried to cast it in varchar which worked but when i read the file content on user request it is not downloaded properly but in binary. Means in case of text file, it shows binary code when downloaded — donstack, Apr 08 '16 at 15:00
What is the content of the text file? Is the chain of numbers the original text or is this somehow encoded? — Shnugo, Apr 09 '16 at 19:44
it is simple text, in XML contains a valid text you can use code from http://www.aspsnippets.com/Articles/Upload-and-Download-files-from-SQL-Server-Database-in-ASPNet.aspx to decode to simple text. used code from here to encode — donstack, Apr 10 '16 at 13:15

score 3 · Answer 1 · edited May 23 '17 at 11:45

Binary data types in SQL Server (including varbinary) are represented as hexadecimal in queries which read and write them.

I think the problem here is that, rather than writing to the database directly from the byte stream (as in the example you linked to in your comment, which would implicitly cast the byte array to a hexadecimal value), it's being written to an intermediate XML block. When the data is written to the XML block, it looks like the byte stream is being converted to a string made up of concatenated list of integers of the byte values in decimal. Because the byte values are not delimited, it might not be possible to reconstruct the original data from this stream.

If you need to use an intermediate XML file, a more conventional approach would be to encode the file data as Base64 in the XML block (as discussed in this question - and doubtless many others). You could then decode it using the xs:base64Binary function:

SET @AttachmentFile = @SubmitXml.value('xs:base64Binary((Values/DealAttachment/AttachmentFile/node())[1])','varbinary(max)')

You are correct about the digits in the XML being decimal ASCII values (+1). — Solomon Rutzky, Apr 10 '16 at 16:25

score 1 · Answer 2 · answered Apr 10 '16 at 16:24

@Ed is correct in that you have somehow stored the decimal ASCII values for each character instead of the hex value for each. You can see that by decoding each one:

SELECT CHAR(99) + CHAR(104) + CHAR(97) + CHAR(110) + CHAR(103) + CHAR(101) +
       CHAR(32) + CHAR(116) + CHAR(104) + CHAR(101);
-- change the

But as you can also see, there is no way to decode that programmatically because it is a mix of 2 digit and 3 digit values.

If you really were storing hex bytes in that XML element, you could turn it into VARBINARY(MAX) having the same binary bytes by first extracting it from the XML as a plain VARCHAR(MAX) and then converting it to VARBINARY(MAX) using the CONVERT built-in function, specifying a "style" of 2.

SELECT CONVERT(VARBINARY(MAX), 'this is a test, yo!');
-- 0x74686973206973206120746573742C20796F21

DECLARE @SomeXML XML = N'
<Values>
  <DealAttachment>
    <AttachmentFile>74686973206973206120746573742C20796F21</AttachmentFile>
  </DealAttachment>
</Values>';

SELECT CONVERT(VARBINARY(MAX),
               @SomeXML.value('(/Values/DealAttachment/AttachmentFile/text())[1]',
                              'VARCHAR(MAX)'),
               2)
-- 0x74686973206973206120746573742C20796F21 (but as VARBINARY)

However, that all being said, ideally you would just Base64 encode the binary file on the way in (also as mentioned by @Ed) using Convert.ToBase64String (since you already have a byte[]).

how to read varbinary data in xml in sql server

2 Answers2