title: Replacing Value of Empty Node in SQL XML
body: <p>I've come across a problem with modifying XML within SQL Server that doesn't seem to make sense to me. I want to update the value of an empty node, but it doesn't seem to be working. For instance, say I have this XML tree:</p>
  
  <pre><code>&lt;root&gt;
      &lt;node /&gt;
  &lt;/root&gt;
  </code></pre>
  
  <p>and I want to update the value of <code>&lt;node /&gt;</code> with 'foo' so I have the XML query:</p>
  
  <pre><code>UPDATE  [Table]
  SET     [XmlColumn].modify('
          replace value of (root/node/text())[1]
          with "foo"')
  </code></pre>
  
  <p>For some reason, this doesn't work. It treats the node like it doesn't exist. If the node already has a value (e.g., <code>&lt;node&gt;bar&lt;/node&gt;</code>), it works just fine, but when the node is empty, it silently ignores the command, without even throwing any errors. Is there a way to make SQL Server acknowledge a <code>replace value of</code> on an empty node?</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>I want the end result of this query to be this:</p>
  
  <pre><code>&lt;root&gt;
      &lt;node&gt;
          foo
      &lt;/node&gt;
  &lt;/root&gt;
  </code></pre>