title: Flattening hierarchical XML in SQL using the nodes() method
body: <p>I have a Stored Procedure that takes an XML document as a parameter similar in a structure to the following:</p>
  
  <pre><code>&lt;grandparent name="grandpa bob"&gt;
    &lt;parent name="papa john"&gt;
      &lt;children&gt;
        &lt;child name="mark" /&gt;
        &lt;child name="cindy" /&gt;
      &lt;/children&gt;
    &lt;/parent&gt;
    &lt;parent name="papa henry"&gt;
      &lt;children&gt;
        &lt;child name="mary" /&gt;
      &lt;/children&gt;
    &lt;/parent&gt;
  &lt;/grandparent&gt;
  </code></pre>
  
  <p>My requirement is to "flatten" this data so that it can be inserted into a temporary table and manipulated further down the procedure, so the above XML becomes:</p>
  
  <pre><code>Grandparent Name Parent Name     Child Name
  ---------------- --------------- ---------------
  grandpa bob      papa john       mark
  grandpa bob      papa john       cindy
  grandpa bob      papa henry      mary
  </code></pre>
  
  <p>This is currently being done using SQL Server XML Nodes:</p>
  
  <pre><code>SELECT
      VIRT.node.value('../../../@name','varchar(15)') 'Grandparent Name',
      VIRT.node.value('../../@name','varchar(15)') 'Parent Name',
      VIRT.node.value('@name','varchar(15)') 'Child Name'
  FROM
      @xmlFamilyTree.nodes('/grandparent/parent/children/child') AS VIRT(node)
  </code></pre>
  
  <p>This works great until I start throwing large amounts of data at the procedure (i.e. 1000+ <code>child</code> nodes), at which point this grinds to a halt and takes between 1 and 2 minutes to execute. I think this may be due to the fact that I am starting off at the lowest level (<code>&lt;child</code>) and then traversing back up the XML document for each occurance. Would splitting this single query into 3 chunks (one per node that I need data from) improve performance here? Given that none of these nodes have "keys" on them that I could use to join back up with, could anyone offer any pointers how I might be able to go about doing this?</p>