Count each element within XML node instead of continuous counting

Question

How to improve this code in the part where the number of SpelementUnit elements are counted (SpelementElement_Count column)?

Instead of a continuous counting within the entire XML I need a SpelementUnit's sequences count separately. (Inside each EntitySpatial--> SpatialElement elements)

While this code counts from 1 to 7. When i need like 1,2 - 1,2,3 - 1,2

CODE

DECLARE @xml XML = 
N'<Parcels>
    <Parcel ID="1">
        <EntitySpatial>
            <SpatialElement>
                <SpelementUnit>
                    <Ordinate X="100.1" Y="-100.1"/>
                </SpelementUnit>
                <SpelementUnit>
                    <Ordinate X="100.2" Y="-100.2"/>
                </SpelementUnit>
            </SpatialElement>
            <SpatialElement>
                <SpelementUnit>
                    <Ordinate X="100.3" Y="-100.3"/>
                </SpelementUnit>
                <SpelementUnit>
                    <Ordinate X="100.4" Y="-100.4"/>
                </SpelementUnit>
                <SpelementUnit>
                    <Ordinate X="100.5" Y="-100.5"/>
                </SpelementUnit>
            </SpatialElement>
        </EntitySpatial>
    </Parcel>
    <Parcel ID="2">
        <EntitySpatial>
            <SpatialElement>
                <SpelementUnit>
                    <Ordinate X="200.1" Y="-200.1"/>
                </SpelementUnit>
                <SpelementUnit>
                    <Ordinate X="200.2" Y="-200.2"/>
                </SpelementUnit>
            </SpatialElement>
        </EntitySpatial>
    </Parcel>
</Parcels>';


SELECT base.value('@ID', 'VARCHAR(1000)') AS Parcel_ID
    , DENSE_RANK() OVER(ORDER BY outr) as SpatialElement_Count
--     ,outr2.value('@PointNum', 'NVARCHAR(1000)') AS PointNum
    ,outr2.value('(Ordinate/@X)[1]', 'NVARCHAR(1000)') AS Ordinate_X
    ,outr2.value('(Ordinate/@Y)[1]', 'NVARCHAR(1000)') AS Ordinate_Y
    , DENSE_RANK() OVER(ORDER BY outr2) as SpelementElement_Count 
FROM @xml.nodes('Parcels/Parcel') as x(base)
    OUTER APPLY base.nodes('EntitySpatial/SpatialElement') AS B(outr)
    OUTER APPLY outr.nodes('SpelementUnit') AS C(outr2);

Desired output (main column is "SpelementElement_Count")

 +-----------+---------------------+-----  -----+------------+------------------------+
| Parcel_ID | SpatialElement_Count | Ordinate_X | Ordinate_Y | SpelementElement_Count |
+-----------+----------------------+------------+------------+------------------------+
|         1 |                    1 |      100.1 |     -100.1 |                      1 |
|         1 |                    1 |      100.2 |     -100.2 |                      2 |
|         1 |                    2 |      100.3 |     -100.3 |                      1 |
|         1 |                    2 |      100.4 |     -100.4 |                      2 |
|         1 |                    2 |      100.5 |     -100.5 |                      3 |
|         2 |                    3 |      200.1 |     -200.1 |                      1 |
|         2 |                    3 |      200.2 |     -200.2 |                      2 |
+-----------+----------------------+------------+------------+------------------------+

Answer 1

You can use PARTITION BY when OVER clause is used. It simply

Divides the query result set into partitions. The window function is applied to each partition separately and computation restarts for each partition.

You can think for PARTITION BY in OVER clause, as GROUP BY clause when aggregate function is used.

SELECT base.value('@ID', 'VARCHAR(1000)') AS Parcel_ID
    , DENSE_RANK() OVER(ORDER BY outr) as SpatialElement_Count
    ,outr2.value('(Ordinate/@X)[1]', 'NVARCHAR(1000)') AS Ordinate_X
    ,outr2.value('(Ordinate/@Y)[1]', 'NVARCHAR(1000)') AS Ordinate_Y
    , DENSE_RANK() OVER(PARTITION BY outr ORDER BY outr2) as SpelementElement_Count 
FROM @xml.nodes('Parcels/Parcel') as x(base)
    OUTER APPLY base.nodes('EntitySpatial/SpatialElement') AS B(outr)
    OUTER APPLY outr.nodes('SpelementUnit') AS C(outr2);