SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Data (
    UID INT,
	Version INT,
	BSpeedsXml XML
)

INSERT Data
VALUES
    (115967, 1, '<BSpeed>
      <Restriction><Speed>60</Speed><TrainType Numeric="2" /></Restriction>
      <Restriction><Speed>40</Speed><TrainType Numeric="1" /></Restriction>
      <Restriction><Speed>40</Speed><TrainType Numeric="3" /></Restriction>
      <Restriction><Speed>60</Speed><TrainType Numeric="6" /></Restriction>
      <Restriction><Speed>60</Speed><TrainType Numeric="5" /></Restriction>
      <Restriction><Speed>60</Speed><TrainType Numeric="4" /></Restriction>
    </BSpeed>'),
    (115967, 2, '<BSpeed>
      <Restriction><Speed>60</Speed><TrainType Numeric="1" /></Restriction>
      <Restriction><Speed>40</Speed><TrainType Numeric="2" /></Restriction>
      <Restriction><Speed>40</Speed><TrainType Numeric="4" /></Restriction>
      <Restriction><Speed>60</Speed><TrainType Numeric="5" /></Restriction>
      <Restriction><Speed>60</Speed><TrainType Numeric="6" /></Restriction>
      <Restriction><Speed>60</Speed><TrainType Numeric="3" /></Restriction>
    </BSpeed>')
2 rows affected

-- Calculate node position by counting preceding sibling nodes.
-- This may become excessively slow as the node count increases.
SELECT  d.UID,
        d.Version,
        a.c.value('Speed[1]','int') as 'Speed',
        a.c.value('TrainType[1]/@Numeric','int') as 'TrainType',
        a.c.value('for $i in . return count(../*[. << $i]) + 1', 'int') AS SequenceNum
FROM Data d
CROSS APPLY d.BSpeedsXml.nodes('/BSpeed/Restriction') a(c)
ORDER BY UID, Version, SequenceNum


UID
Version
Speed
TrainType
SequenceNum


115967
1
60
2
1

115967
1
40
1
2

115967
1
40
3
3

115967
1
60
6
4

115967
1
60
5
5

115967
1
60
4
6

115967
2
60
1
1

115967
2
40
2
2

115967
2
40
4
3

115967
2
60
5
4

115967
2
60
6
5

115967
2
60
3
6


-- Generate position numbers using ROW_NUMBER() OVER(ORDER BY xml_node).
-- (This is potentially the best answer, but I cannot find documentation
-- stating that "ORDER BY xml_node" is well-defined.)
SELECT  d.UID,
        d.Version,
        a.c.value('Speed[1]','int') as 'Speed',
        a.c.value('TrainType[1]/@Numeric','int') as 'TrainType',
        row_number() over (partition by UID, Version order by a.c) AS SequenceNum
FROM Data d
CROSS APPLY d.BSpeedsXml.nodes('/BSpeed/Restriction') a(c)
ORDER BY UID, Version, SequenceNum


UID
Version
Speed
TrainType
SequenceNum


115967
1
60
2
1

115967
1
40
1
2

115967
1
40
3
3

115967
1
60
6
4

115967
1
60
5
5

115967
1
60
4
6

115967
2
60
1
1

115967
2
40
2
2

115967
2
40
4
3

115967
2
60
5
4

115967
2
60
6
5

115967
2
60
3
6


-- Count the nodes, generate sequence numbers, and then access the data using those
-- sequence numbers as indexes.
-- Note: GENERATE_SERIES() function is only available in SQL Server 2022 and later.
SELECT  d.UID,
        d.Version,
        a.c.value('Speed[1]','int') as 'Speed',
        a.c.value('TrainType[1]/@Numeric','int') as 'TrainType',
        s.value AS SequenceNum
FROM Data d
CROSS APPLY GENERATE_SERIES(1, d.BSpeedsXml.value('count(/BSpeed/Restriction)', 'int')) s
CROSS APPLY d.BSpeedsXml.nodes('/BSpeed/Restriction[sql:column("s.value")]') a(c)
ORDER BY UID, Version, SequenceNum


UID
Version
Speed
TrainType
SequenceNum


115967
1
60
2
1

115967
1
40
1
2

115967
1
40
3
3

115967
1
60
6
4

115967
1
60
5
5

115967
1
60
4
6

115967
2
60
1
1

115967
2
40
2
2

115967
2
40
4
3

115967
2
60
5
4

115967
2
60
6
5

115967
2
60
3
6


-- Alternative to GENERATE_SERIES() for SQL Server versions prior to 2022.
-- Count the nodes, generate sequence numbers, and then access the data using those
-- sequence numbers as indexes.
SELECT  d.UID,
        d.Version,
        a.c.value('Speed[1]','int') as 'Speed',
        a.c.value('TrainType[1]/@Numeric','int') as 'TrainType',
        s.value AS SequenceNum
FROM Data d
CROSS APPLY ( -- Alternative to GENERATE_SERIES() for older SQL Server versions
    SELECT TOP (d.BSpeedsXml.value('count(/BSpeed/Restriction)', 'int'))
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS value
    FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) n1(value)
    CROSS JOIN (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) n2(value) -- up to 100
    CROSS JOIN (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) n3(value) -- up to 1000
    CROSS JOIN (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) n4(value) -- up to 10,000
    -- Add more CROSS JOINs if needed based on expected max count.
) s
CROSS APPLY d.BSpeedsXml.nodes('/BSpeed/Restriction[sql:column("s.value")]') a(c)
ORDER BY UID, Version, SequenceNum


UID
Version
Speed
TrainType
SequenceNum


115967
1
60
2
1

115967
1
40
1
2

115967
1
40
3
3

115967
1
60
6
4

115967
1
60
5
5

115967
1
60
4
6

115967
2
60
1
1

115967
2
40
2
2

115967
2
40
4
3

115967
2
60
5
4

115967
2
60
6
5

115967
2
60
3
6
UID	Version	Speed	TrainType	SequenceNum
115967	1	60	2	1
115967	1	40	1	2
115967	1	40	3	3
115967	1	60	6	4
115967	1	60	5	5
115967	1	60	4	6
115967	2	60	1	1
115967	2	40	2	2
115967	2	40	4	3
115967	2	60	5	4
115967	2	60	6	5
115967	2	60	3	6