title: how to read varbinary data in xml in sql server
body: <p>i've data in XML like </p>
  
  <pre><code>&lt;Values&gt;
    &lt;Id&gt;7f8a5d20-d171-42f5-a222-a01b5186a048&lt;/Id&gt;
    &lt;DealAttachment&gt;
      &lt;AttachmentId&gt;deefff3f-f63e-4b4c-8e76-68b6db476628&lt;/AttachmentId&gt;
      &lt;IsNew&gt;true&lt;/IsNew&gt;
      &lt;IsDeleted&gt;false&lt;/IsDeleted&gt;
      &lt;DealId&gt;7f8a5d20-d171-42f5-a222-a01b5186a048&lt;/DealId&gt;
      &lt;AttachmentName&gt;changes2&lt;/AttachmentName&gt;
      &lt;AttachmentFile&gt;991049711010310132116104101321011099710510832115117981061019911645100111110101131011711210097116101329811110012132116101120116321151171031031011151161011003298121326610510810845100111110101131010011132110111116321151011101003210110997105108321051103299971151013211110232117112100971161011003299108105101110116115329811711632115101110100321051103299971151013211110232100101108101116101100329910810510111011611545321001111101011310991141019711610132973211510111297114971161013211697981081013211611132991111101169710511032117115101114115321161113211910411110932101109971051083211910510810832981013211510111011645100111110101131011611411711099971161013211610410132108111103321169798108101329710211610111432111110101321091111101161044510011111010113108411497110115108971161051111101154532100111110101131097100100321129711497109101116101114321161113210611198321161113211411711032115105108101110116108121451001011089712110110013101310131013101310131010511010211111410932981051081083210511032999711510132981111161043211711210097116105111110471001011081011161051111104432117112100971161051111103211910510810832110111116329810132105110102111114109101100&lt;/AttachmentFile&gt;
      &lt;AttachmentType&gt;.txt&lt;/AttachmentType&gt;
    &lt;/DealAttachment&gt;
  &lt;/Values&gt;
  </code></pre>
  
  <p>where AttachmentFile is varbinary(max)</p>
  
  <pre><code>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)')
  </code></pre>
  
  <p>But, after above statement @AttachmentFile is NULL or blankspace.</p>