title: How to remove xml nodes without attribute in Sql Server
body: <p>What is the correct way to remove all child nodes of the root node with a missing attribute from an xml field in Sql Server 2008?</p>
  
  <p>My Xml looks like this, I want to remove all the child nodes of <code>&lt;root&gt;</code> that don't have the <code>ln</code> attribute specified</p>
  
  <pre><code>&lt;root&gt;
    &lt;title /&gt;
    &lt;title /&gt;
    &lt;questionphrase ln="nl"&gt;
      &lt;xhtml /&gt;
    &lt;/questionphrase&gt;
    &lt;questionphrase ln="en"&gt;
      &lt;xhtml /&gt;
    &lt;/questionphrase&gt;
    &lt;information ln="nl"&gt;
      &lt;xhtml /&gt;
    &lt;/information&gt;
    &lt;information ln="en"&gt;
      &lt;xhtml /&gt;
    &lt;/information&gt;
    &lt;title /&gt;
    &lt;title ln="en"&gt;
       value
    &lt;/title&gt;
    &lt;label ln="en"&gt;
       value
    &lt;/label&gt;
    &lt;title /&gt;
    &lt;title /&gt;
  &lt;/root&gt;
  </code></pre>
  
  <p>After the delete the xml should look like this</p>
  
  <pre><code>&lt;root&gt;
    &lt;questionphrase ln="nl"&gt;
      &lt;xhtml /&gt;
    &lt;/questionphrase&gt;
    &lt;questionphrase ln="en"&gt;
      &lt;xhtml /&gt;
    &lt;/questionphrase&gt;
    &lt;information ln="nl"&gt;
      &lt;xhtml /&gt;
    &lt;/information&gt;
    &lt;information ln="en"&gt;
      &lt;xhtml /&gt;
    &lt;/information&gt;
    &lt;title ln="en"&gt;
       value
    &lt;/title&gt;
    &lt;label ln="en"&gt;
       value
    &lt;/label&gt;
  &lt;/root&gt;
  </code></pre>