Numbering sequences from xml according element's values

Question

The PointNum attribute means the number of coordinates in the chain. The coordinate chain starts at 1 and ends at 1 as well (inside each Parcel element).

However, within the sequences of these points, there is one more inner logic, which must be explicated. This is the logic of full coincidence of the X and Y coordinates. For example:

• in the sequence within Parcel_ID = 1 there are equal coordinates with PointNum 1 and 4, 5 and 7

• in the sequence within Parcel_ID = 2 there are equal coordinates with PointNum 1 и 3

GOAL: I need to number these sub-chains also. (in the SequenceNum column)

CODE

DECLARE @xml XML = 
N'<Parcels>
    <Parcel ID="1">
        <EntitySpatial>
            <SpatialElement>

                <SpelementUnit PointNum="1">
                    <Ordinate X="100.1" Y="-100.1"/>
                </SpelementUnit>
                <SpelementUnit PointNum="2">
                    <Ordinate X="100.2" Y="-100.2"/>
                </SpelementUnit>
                <SpelementUnit PointNum="3">
                    <Ordinate X="100.3" Y="-100.3"/>
                </SpelementUnit>
                <SpelementUnit PointNum="4">
                    <Ordinate X="100.1" Y="-100.1"/>
                </SpelementUnit>

                <SpelementUnit PointNum="5">
                    <Ordinate X="100.5" Y="-100.5"/>
                </SpelementUnit>
                <SpelementUnit PointNum="6">
                    <Ordinate X="100.6" Y="-100.6"/>
                </SpelementUnit>
                <SpelementUnit PointNum="7">
                    <Ordinate X="100.5" Y="-100.5"/>
                </SpelementUnit>

                <SpelementUnit PointNum="8">
                    <Ordinate X="100.8" Y="-100.8"/>
                </SpelementUnit>
                <SpelementUnit PointNum="9">
                    <Ordinate X="100.9" Y="-100.9"/>
                </SpelementUnit>
                <SpelementUnit PointNum="10">
                    <Ordinate X="100.11" Y="-100.11"/>
                </SpelementUnit>
                <SpelementUnit PointNum="1">
                    <Ordinate X="100.1" Y="-100.1"/>
                </SpelementUnit>

            </SpatialElement>
        </EntitySpatial>
    </Parcel>
    <Parcel ID="2">
        <EntitySpatial>
            <SpatialElement>

                <SpelementUnit PointNum="1">
                    <Ordinate X="200.1" Y="-200.1"/>
                </SpelementUnit>
                <SpelementUnit PointNum="2">
                    <Ordinate X="200.2" Y="-200.2"/>
                </SpelementUnit>
                <SpelementUnit PointNum="3">
                    <Ordinate X="200.1"" Y="-200.1"/>
                </SpelementUnit>

                <SpelementUnit PointNum="4">
                    <Ordinate X="200.4" Y="-200.4"/>
                </SpelementUnit>
                <SpelementUnit PointNum="5">
                    <Ordinate X="200.5" Y="-200.5"/>
                </SpelementUnit>
                <SpelementUnit PointNum="1">
                    <Ordinate X="200.1" Y="-200.1"/>
                </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('(Ordinate/@X)[1]', 'NVARCHAR(1000)') AS Ordinate_X
    ,outr2.value('(Ordinate/@Y)[1]', 'NVARCHAR(1000)') AS Ordinate_Y
    ,outr2.value('@PointNum', 'NVARCHAR(1000)') AS PointNum
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 ("SequenceNum" column)

+-----------+----------------------+------------+------------+--------------------------+
| Parcel_ID | SpatialElement_Count | Ordinate_X | Ordinate_Y |  PointNum |  SequenceNum |
+-----------+----------------------+------------+------------+--------------------------+
|         1 |                    1 |      100.1 |     -100.1 |         1 |        1     |
|         1 |                    1 |      100.2 |     -100.2 |         2 |        1     |
|         1 |                    1 |      100.3 |     -100.3 |         3 |        1     |
|         1 |                    1 |      100.1 |     -100.1 |         4 |        1     |
|           |                      |            |            |           |              | 
|         1 |                    1 |      100.5 |     -100.5 |         5 |        2     |
|         1 |                    1 |      100.6 |     -100.6 |         6 |        2     |
|         1 |                    1 |      100.5 |     -100.5 |         7 |        2     |
|           |                      |            |            |           |              |  
|         1 |                    1 |      100.8 |     -100.8 |         8 |      null    |
|         1 |                    1 |      100.9 |     -100.9 |         9 |      null    |
|         1 |                    1 |     100.11 |    -100.11 |        10 |      null    |
|         1 |                    1 |      100.1 |     -100.1 |         1 |      null    |
|           |                      |            |            |           |              |  
|           |                      |            |            |           |              |  
|         2 |                    2 |      200.1 |     -200.1 |         1 |     1(or 3)  |
|         2 |                    2 |      200.2 |     -200.2 |         2 |     1(or 3)  |
|         2 |                    2 |      200.1 |     -200.1 |         3 |     1(or 3)  |
|           |                      |            |            |           |              |  
|         2 |                    2 |      200.4 |     -200.4 |         4 |      null    |
|         2 |                    2 |      200.5 |     -200.5 |         5 |      null    |
|         2 |                    2 |      200.1 |     -200.1 |         1 |      null    |
+-----------+----------------------+------------+------------+--------------------------+

Answer 1

This might help you. Please note that it works for your example but I can see it failing for cases like...

1. More than two equal coordinates.
2. Intertwined coordinates groups.
3. PointNum attribute jumps (breaks in the ordinal sequence)

WITH parseXml AS (
    SELECT
        base.value('@ID', 'VARCHAR(1000)') AS Parcel_ID,
        outr.value('for $se in . return count(//SpatialElement[. << $se]) + 1', 'INTEGER') AS SpatialElement_Count,
        outr2.value('for $o in . return count(../SpelementUnit[. << $o]) + 1', 'INTEGER') AS Ordinate_position,
        outr2.value('@PointNum', 'INTEGER') AS PointNum,
        outr2.value('(Ordinate/@X)[1]', 'NVARCHAR(100)') AS Ordinate_X,
        outr2.value('(Ordinate/@Y)[1]', 'NVARCHAR(100)') AS Ordinate_Y
    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)
),
groups AS (
    SELECT
        Parcel_ID, Ordinate_X, Ordinate_Y,
        --Exclude last row in parcel_id
        MIN(CASE WHEN PointNum = Ordinate_position THEN Ordinate_position END) AS StartSequence,
        MAX(CASE WHEN PointNum = Ordinate_position THEN Ordinate_position END) AS EndSequence,
        ROW_NUMBER() OVER(PARTITION BY Parcel_ID
            ORDER BY MIN(CASE WHEN PointNum = Ordinate_position THEN Ordinate_position END)) AS SequenceNum
    FROM
        parseXml
    GROUP BY
        Parcel_ID, Ordinate_X, Ordinate_Y
    HAVING COUNT(*) > 1
)
SELECT
    ref.Parcel_ID, ref.SpatialElement_Count, ref.Ordinate_X, ref.Ordinate_Y, ref.PointNum,
    groups.SequenceNum
FROM
    parseXml ref
    LEFT JOIN groups
        ON groups.StartSequence <= ref.Ordinate_position
        AND groups.EndSequence >= ref.Ordinate_position
        AND groups.Parcel_ID = ref.Parcel_ID
ORDER BY
    ref.Parcel_ID, Ordinate_position;