Query (SQL Server) to get value from node in XML column stored as varchar(max)?
<p>I hate dealing with XML queries. I don't do it often enough to remember how to format everything, but I'm at my wits' end.
Let's say I have a table called Messages and a column inside it called Payload. Payload contains XML stored as varchar(max). The XML is formatted as such:</p>

<p><div class="snippet" data-lang="js" data-hide="false" data-console="true">
<div class="snippet-code">
<pre class="snippet-code-html lang-html prettyprint-override"><code>&lt;NCOAPACP xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="uri://www.gen.da.hob/VC/Contract/QualityManagement/EMSM"&gt;
  
&lt;VID&gt;3656183&lt;/VID&gt;
  
&lt;/NCOAPACP&gt;</code></pre>
</div>
</div>
</p>

<p>How do I query the table to retrieve a list of the values in the VID node?</p>