title: SQL Server - returning xml child nodes for xml column
body: <p>Given table T with columns:<br></p>
  
  <pre><code>ID UNIQUEIDENTIFIER
  CreatedDate DATETIME
  XmlData XML
  </code></pre>
  
  <p>Where XmlData is structured like:</p>
  
  <pre><code>&lt;application&gt;
      &lt;details firstname="first" lastname="last"&gt;
          &lt;statement&gt;statement&lt;/statement&gt;
      &lt;/details&gt;
      &lt;educationHistory&gt;
          &lt;education subject="subject1" /&gt;
          &lt;education subject="subject2" /&gt;
      &lt;/educationHistory&gt;
      &lt;experienceHistory&gt;
          &lt;examiningExperienceHistory&gt;
              &lt;examiningExperience module="module1" /&gt;
              &lt;examiningExperience module="module2" /&gt;
          &lt;/examiningExperienceHistory&gt;
          &lt;teachingExperienceHistory&gt;
              &lt;teachingExperience module="module1" /&gt;
              &lt;teachingExperience module="module2" /&gt;
          &lt;/teachingExperienceHistory&gt;
      &lt;/experienceHistory&gt;
  &lt;/application&gt;
  </code></pre>
  
  <p>I need to return an extract like so:</p>
  
  <pre><code>ID Date       FirstName LastName Education    ExaminingExp TeachingExp
  -----------------------------------------------------------------------
  1  02-10-2012 First     Last     &lt;xmlextract&gt; &lt;xmlextract&gt; &lt;xmlextract&gt;
  </code></pre>
  
  <p>So far I have:</p>
  
  <pre><code>SELECT ID,
         CreatedDate [Date],
         XmlData.value('(application/details/@firstname)[1]','varchar(max)') [FirstName],
         XmlData.value('(application/details/@lastname)[1]','varchar(max)') [LastName]
  FROM T
  </code></pre>
  
  <p>I'm struggling with thee last three columns. For each record, I need to list teaching/examining experience, and education. Can anybody help?</p>