title: Using HASHBYTES on large XML sets
body: <p>I am using the <code>SQL Broker</code> &amp; <code>SQL CLR</code> to push large sets of <code>XML</code> out to my Enterprise Service Bus (ESB) for consumers to pick-up and process.  In an effort to "audit" OUTGOING XML (I match it) with with XML RECEIVED by the service bus (by saving both into a table).  </p>
  
  <p>Saving LARGE sets of XML is easy...no problems there.  But I am getting the following error when I use HASHBYTES on LARGE XML values:</p>
  
  <ul>
  <li>String or binary data would be truncated.</li>
  </ul>
  
  <p>Oddly...</p>
  
  <ul>
  <li>Converting to VACHAR(MAX) isn't the issue</li>
  <li>Using HASHBYTES is what fails</li>
  </ul>
  
  <p><strong>THE GOAL:</strong><br />
  I need to HASH the values or reduce them into a CHECKSUM so they can easily be validated.</p>
  
  <p><strong>As such, how can I HASH or REDUCE these large XML values into a single (small) value that can be easily &amp; visually validated?</strong></p>
  
  <p><strong>SAMPLE TESTER:</strong><br />
  Inserting LARGE XML into this question would be too cumbersome, so I am including a mini-script which replicates the issue:</p>
  
  <pre><code>DECLARE @ContextCount INT = 1000;
  
  ----------------
  ---- Build the LARGE XML
  ----------------
  DECLARE @Count INT = 1;
  DECLARE @Xml NVARCHAR(MAX) = '&lt;Contexts&gt;';
  WHILE (@Count &lt;= @ContextCount)
  BEGIN
      SET @Xml = @Xml + '&lt;Context&gt;&lt;Name&gt;SomeTableName&lt;/Name&gt;&lt;Key&gt;&lt;SomeColumnName&gt;' + CONVERT(VARCHAR(10), @Count) + '&lt;/SomeColumnName&gt;&lt;/Key&gt;&lt;/Context&gt;'
      SET @Count = @Count + 1
  END
  SET @Xml = @Xml + '&lt;/Contexts&gt;'
  
  ----------------
  ---- Build the LARGE XML
  ----------------
  
  -- THIS WORKS
  SELECT CONVERT(VARCHAR(MAX), @Xml)
  
  -- THIS FAILS
  SELECT HASHBYTES('MD5', CONVERT(VARCHAR(MAX), @Xml));
  </code></pre>