Getting the absolute position of a node using sql

Question

I have an xml document of the bible

as

<bookcoll>
<book>
<bktshort>Matthew</bktshort>
<chapter><chtitle>Chapter 1</chtitle>
<v>The book of the generation of Jesus Christ, the son of David, the son of Abraham.
</v>
<v>Abraham begat Isaac; and Isaac begat Jacob; and Jacob begat Judas and his brethren;
</v>
..
</chapter>
<chapter><chtitle>Chapter 2</chtitle>
<v>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,
</v>

I would like to keep a row number of the total nuber of <v> nodes.

This statement resets the number for each Chapter node

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

so instead of

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.   

I would want

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   <b>26</b>   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   <b>27</b>    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.   

Side note I know that spt_values table is not big enough and would have to use a local table.

Answer 1

MY First solution is

SELECT 
    BQ.BookName
,   BQ.BookNumber
,   CQ.ChapterTitle
,   CQ.ChapterNumber
,   VQ.VerseText
,   VQ.VerseNumber
,   ROW_NUMBER() OVER (ORDER BY BQ.BookNumber, CQ.ChapterNumber,VQ.VerseNumber)
FROM
    (SELECT Books.value('bktshort[1]', 'varchar(200)') as BookName,
        p.number as BookNumber
    FROM master..spt_values p
    CROSS APPLY
        @xml.nodes('/bookcoll/book[position()=sql:column("number")]') B(Books)
    WHERE p.type='p'
    ) BQ
INNER JOIN
    (SELECT C.Chapter.value('../bktshort[1]', 'varchar(200)') as BookName,
    C.Chapter.value('chtitle[1]', 'varchar(200)') as ChapterTitle,
        p.number as ChapterNumber
    FROM master..spt_values p
    CROSS APPLY
        @xml.nodes('/bookcoll/book/chapter[position()=sql:column("number")]') C(Chapter)
    WHERE p.type='p'
    ) CQ
    on BQ.BookName = CQ.BookName
INNER JOIN
    (SELECT V.Verses.value('../../bktshort[1]', 'varchar(200)') as BookName,
    V.Verses.value('../chtitle[1]', 'varchar(200)') as ChapterTitle,
    V.Verses.value('.', 'varchar(max)') as VerseText,
        p.number as VerseNumber
    FROM master..spt_values p
    CROSS APPLY
        @xml.nodes('/bookcoll/book/chapter/v[position()=sql:column("number")]') V(Verses)
    WHERE p.type='p'
    ) VQ
    on CQ.BookName = VQ.BookName
    and CQ.ChapterTitle = VQ.ChapterTitle

but this takes two minutes to run so I am still taking suggestions