title: SQL server insert data from table into an XML variable
body: <p>How can I insert a whole bunch of rows into an XML variable without using a cursor?
  I know I can do</p>
  
  <pre><code>SET @errors.modify('insert &lt;error&gt;{ sql:variable("@text") }&lt;/error&gt; as last into /errors[1]')
  </code></pre>
  
  <p>to insert the value of a variable, but I want to basically do</p>
  
  <pre><code>SET @errors.modify(SELECT 'insert &lt;error&gt;{ sql:column("text") }&lt;/error&gt;' FROM table)
  </code></pre>
  
  <p>which, of course, isn't legal syntax.</p>
  
  <p>Edit: Obviously my question wasn't clear. What I want is to be able to do like this:</p>
  
  <pre><code>CREATE TABLE my_table(text nvarchar(50))
  INSERT INTO my_table VALUES('Message 2')
  INSERT INTO my_table VALUES('Message 3')
  
  DECLARE @errors xml
  SET @errors = '&lt;errors&gt;&lt;error&gt;Message 1&lt;/error&gt;&lt;/errors&gt;'
  
  SET @errors.modify('INSERT EVERYTHING FROM my_table MAGIC STATEMENT')
  </code></pre>
  
  <p>And after running this code, @errors should contain </p>
  
  <pre><code>&lt;errors&gt;
    &lt;error&gt;Message 1&lt;/error&gt;
    &lt;error&gt;Message 2&lt;/error&gt;
    &lt;error&gt;Message 3&lt;/error&gt;
  &lt;/errors&gt;
  </code></pre>