title: Parse XML to Table in SQL (Only nodes that have values)
body: <p>I am trying to find a way to parse a rather large XML file into a table that simply shows the name of the node, and it's value. There are no attributes to worry about in the XML, but there are several nested nodes.</p>
  
  <p>Because there the XML is generated dynamically, there is no way of knowing the exact depth of that nesting. But it is safe to assume only the <b>LAST CHILD NODE</b> of any branch will contain data.</p>
  
  <p>This is my attempt at a solution, but the problem is that when accessing a parent node, the data from every child within gets concatenated as the value.</p>
  
  <pre><code>DECLARE @ProjectXML XML
  SET @ProjectXML = '
  &lt;Project&gt;
  
    &lt;ProjectId&gt;1&lt;/ProjectId&gt;
    &lt;OrganizationId&gt;1&lt;/OrganizationId&gt;
    &lt;Title&gt;This is a Test Title&lt;/Title&gt;
    &lt;Description&gt;This is a Test Description&lt;/Description&gt;
    &lt;ProjectType&gt;
      &lt;Id&gt;1&lt;/Id&gt;
      &lt;Name&gt;Test Name&lt;/Name&gt;
      &lt;Outer&gt;
          &lt;Inner1&gt;Hi&lt;/Inner1&gt;
          &lt;Inner2&gt;There&lt;/Inner2&gt;
      &lt;/Outer&gt;
    &lt;/ProjectType&gt;
  &lt;/Project&gt;
  '
  SELECT
  n.value('local-name(.)','VARCHAR(100)') AS Name,  
  n.value('.','VARCHAR(MAX)') AS Value
  
  FROM @ProjectXML.nodes('//*') AS ProjectXML(n) 
  </code></pre>
  
  <p>Which results in:</p>
  
  <pre><code>Project           11This is a Test TitleThis is a Test Description1Test NameHiThere
  ProjectId         1
  OrganizationId    1
  Title             This is a Test Title
  Description       This is a Test Description
  ProjectType       1Test NameHiThere
  Id                1
  Name              Test Name
  Outer             HiThere
  Inner1            Hi
  Inner2            There
  </code></pre>
  
  <p>Is there a way (using xquery perhaps?) to ignore the parent nodes which contain no data, and only return values for the child nodes which do?</p>
  
  <p>**Bonus points if it is possible to return the Name of each result as a formatted list of it's parent nodes , such as:</p>
  
  <pre><code>Project - ProjectId                         1
  Project - OrganizationId                    1
  Project - Title                             This is a Test Title
  Project - Description                       This is a Test Description
  Project - Project Type - Id                 1
  Project - Project Type - Name               Test Name
  Project - Project Type - Outer - Inner1     Hi
  Project - Project Type - Outer - Inner2     There
  </code></pre>