title: Finding node order in XML document in SQL Server
body: <p>How can I find the order of nodes in an XML document?</p>
  
  <p>What I have is a document like this:</p>
  
  <pre><code>&lt;value code="1"&gt;
      &lt;value code="11"&gt;
          &lt;value code="111"/&gt;
      &lt;/value&gt;
      &lt;value code="12"&gt;
          &lt;value code="121"&gt;
              &lt;value code="1211"/&gt;
              &lt;value code="1212"/&gt;
          &lt;/value&gt;
      &lt;/value&gt;
  &lt;/value&gt;
  </code></pre>
  
  <p>and I'm trying to get this thing into a table defined like</p>
  
  <pre><code>CREATE TABLE values(
      code int,
      parent_code int,
      ord int
  )
  </code></pre>
  
  <p>Preserving the order of the values from the XML document (they can't be ordered by their code). I want to be able to say</p>
  
  <pre><code>SELECT code 
  FROM values 
  WHERE parent_code = 121 
  ORDER BY ord
  </code></pre>
  
  <p>and the results should, deterministically, be </p>
  
  <pre><code>code
  1211
  1212
  </code></pre>
  
  <p>I have tried</p>
  
  <pre><code>SELECT 
      value.value('@code', 'varchar(20)') code, 
      value.value('../@code', 'varchar(20)') parent, 
      value.value('position()', 'int')
  FROM @xml.nodes('/root//value') n(value)
  ORDER BY code desc
  </code></pre>
  
  <p>But it doesn't accept the <code>position()</code> function ('<code>position()</code>' can only be used within a predicate or XPath selector).</p>
  
  <p>I guess it's possible some way, but how?</p>