title: Modify XML in SQL server to add a root node
body: <p>To give some background to this problem first, I am rewriting some code that currently loops through some xml, doing an insert to a table at the end of each loop - replacing with a single sp that takes an xml parameter and does the insert in one go, 'shredding' the xml into a table.  </p>
  
  <p>The main shred has been done successfully,but currently one of the columns is used to store the entire node. I have been able to work out the query necessary for this (almost), but it misses out the root part of the node. I have come to the conclusion that my query is as good as I can get it, and I am looking at a way to then do an update statement to get the root node back in there.  </p>
  
  <p>So my xml is of the form;  </p>
  
  <pre><code>&lt;xml&gt;
  &lt;Items&gt;
  &lt;Item&gt;
      &lt;node1&gt;...&lt;/node1&gt;&lt;node2&gt;..&lt;node2&gt;.....&lt;noden&gt;...&lt;noden&gt;
  &lt;Item&gt;
  &lt;Item&gt;
      &lt;node1&gt;...&lt;/node1&gt;&lt;node2&gt;..&lt;node2&gt;.....&lt;noden&gt;...&lt;noden&gt;
  &lt;Item&gt;
  &lt;Item&gt;
      &lt;node1&gt;...&lt;/node1&gt;&lt;node2&gt;..&lt;node2&gt;.....&lt;noden&gt;...&lt;noden&gt;
  &lt;Item&gt;
  ......
  &lt;Items&gt;
  &lt;/xml&gt;
  </code></pre>
  
  <p>So the basic shredding puts the value from node1 into column1, node2 into column2 etc. The insert statement looks something like;  </p>
  
  <pre><code>INSERT INTO mytable col1, col2,...etc.....,wholenodecolumn
  Select  
  doc.col.value('node1[1]', 'int') column1,
  doc.col.value('node2[1]', 'varchar(50)') column2,
  ....etc......,
  doc.col.query('*')--this is the query for getting the whole node
  FROM @xml.nodes('//Items/Item') doc(col)
  </code></pre>
  
  <p>The XML that ends up in wholenodecolumn is of the form;  </p>
  
  <pre><code>&lt;node1&gt;...&lt;/node1&gt;&lt;node2&gt;..&lt;node2&gt;.....&lt;noden&gt;...&lt;noden&gt;
  </code></pre>
  
  <p>but I need it to be of the form </p>
  
  <pre><code>&lt;Item&gt;&lt;node1&gt;...&lt;/node1&gt;&lt;node2&gt;..&lt;node2&gt;.....&lt;noden&gt;...&lt;noden&gt;&lt;/Item&gt;
  </code></pre>
  
  <p>There is existing code (a lot of it) that depends on the xml in this column being of the correct form.  </p>
  
  <p>So can someone maybe see how to modify the <code>doc.col.query('*')</code> to get the desired result?  </p>
  
  <p>Anyway, I gave up on modifying the query, and tried to think of other ways to accomplish the end result. What I am now looking at is an Update after the insert- something like;  </p>
  
  <pre><code>update mytable set wholenodecolumn.modify('insert &lt;Item&gt; as first before * ')
  </code></pre>
  
  <p>If I could do this along with  </p>
  
  <pre><code> .modify('insert &lt;/Item&gt; as last after * ')  
  </code></pre>
  
  <p>that would be fine, but doing 1 at a time isn't an option as the XML is then invalid  </p>
  
  <pre><code>XQuery [mytable.wholenodecolumn.modify()]: Expected end tag 'Item'  
  </code></pre>
  
  <p>and doing both together I don't know if it's possible but I've tried various syntax and can't get to work.  </p>
  
  <p>Any other approaches to the problem also gratefully received</p>