<p>I want to generate some XML in a stored procedure based on data in a table.</p>

<p>The following insert allows me to add many nodes but they have to be hard-coded or use variables (sql:variable):</p>

<pre><code>SET @MyXml.modify('
      insert
         &lt;myNode&gt;
            {sql:variable("@MyVariable")}
         &lt;/myNode&gt;
      into (/root[1]) ') 
</code></pre>

<p>So I could loop through each record in my table, put the values I need into variables and execute the above statement.</p>

<p>But is there a way I can do this by just combining with a select statement and avoiding the loop?</p>

<p><strong>Edit</strong> I have used <code>SELECT FOR XML</code>  to do similar stuff before but I always find it hard to read when working with a hierarchy of data from multiple tables. I was hoping  there would be something using the <code>modify</code> where the XML generated is more explicit and more controllable.</p>

Can you tell a bit more about what exactly you are planning to do.
Is it simply generating XML data based on a content of the table 
or adding some data from the table to an existing xml structure?
There are
great series of articles
on the subject on XML in SQLServer written by Jacob Sebastian, it starts with the
basics of generating XML from the data in the table