I want to generate some XML in a stored procedure based on data in a table.

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

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

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

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

Edit I have used SELECT FOR XML 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 modify where the XML generated is more explicit and more controllable.