title: Getting the absolute position of a node using sql
body: <p>I have an xml document of the bible</p>
  
  <p>as </p>
  
  <pre><code>&lt;bookcoll&gt;
  &lt;book&gt;
  &lt;bktshort&gt;Matthew&lt;/bktshort&gt;
  &lt;chapter&gt;&lt;chtitle&gt;Chapter 1&lt;/chtitle&gt;
  &lt;v&gt;The book of the generation of Jesus Christ, the son of David, the son of Abraham.
  &lt;/v&gt;
  &lt;v&gt;Abraham begat Isaac; and Isaac begat Jacob; and Jacob begat Judas and his brethren;
  &lt;/v&gt;
  ..
  &lt;/chapter&gt;
  &lt;chapter&gt;&lt;chtitle&gt;Chapter 2&lt;/chtitle&gt;
  &lt;v&gt;Now when Jesus was born in Bethlehem of Judaea in the days of Herod the king, behold, there came wise men from the east to Jerusalem,
  &lt;/v&gt;
  </code></pre>
  
  <p>I would like to keep a row number of the total nuber of <code>&lt;v&gt;</code> nodes.</p>
  
  <p>This statement resets the number for each Chapter node</p>
  
  <pre><code>select 
      Chapter.value('../../bktshort[1]', 'varchar(200)'),
      Replace(Chapter.value('../chtitle[1]', 'varchar(200)'),'Chapter ', ''),
      p.number,
      Chapter.value('.','varchar(max)')
  from
      master..spt_values p
  CROSS APPLY
      @xml.nodes('/bookcoll/book/chapter/v[position()=sql:column("number")]') T(Chapter)
  --where p.type = 'p'
  order by 
  Chapter.value('../../bktshort[1]', 'varchar(200)'),
  Replace(Chapter.value('../chtitle[1]', 'varchar(200)'),'Chapter ', ''),
      p.number
  </code></pre>
  
  <p>so instead of </p>
  
  <pre><code>Matthew 1   23  Behold, a virgin shall be with child, and shall bring forth a son, and they shall call his name Emmanuel, which being interpreted is, God with us.   
  Matthew 1   24  Then Joseph being raised from sleep did as the angel of the Lord had bidden him, and took unto him his wife:   
  Matthew 1   25  And knew her not till she had brought forth her firstborn son: and he called his name JESUS.   
  Matthew 2   1   Now when Jesus was born in Bethlehem of Judaea in the days of Herod the king, behold, there came wise men from the east to Jerusalem,   
  Matthew 2   2   Saying, Where is he that is born King of the Jews? for we have seen his star in the east, and are come to worship him.   
  </code></pre>
  
  <p>I would want</p>
  
  <pre><code>Matthew 1   23  Behold, a virgin shall be with child, and shall bring forth a son, and they shall call his name Emmanuel, which being interpreted is, God with us.   
  Matthew 1   24  Then Joseph being raised from sleep did as the angel of the Lord had bidden him, and took unto him his wife:   
  Matthew 1   25  And knew her not till she had brought forth her firstborn son: and he called his name JESUS.   
  Matthew 2   &lt;b&gt;26&lt;/b&gt;   Now when Jesus was born in Bethlehem of Judaea in the days of Herod the king, behold, there came wise men from the east to Jerusalem,   
  Matthew 2   &lt;b&gt;27&lt;/b&gt;    Saying, Where is he that is born King of the Jews? for we have seen his star in the east, and are come to worship him.   
  </code></pre>
  
  <p>Side note I know that <code>spt_values</code> table is not big enough and would have to use a local table.</p>