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
x
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 PointNumFROM @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 |+-----------+----------------------+------------+------------+--------------------------+Advertisement
Answer
This might help you. Please note that it works for your example but I can see it failing for cases like…
- More than two equal coordinates.
- Intertwined coordinates groups.
- 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.SequenceNumFROM parseXml ref LEFT JOIN groups ON groups.StartSequence <= ref.Ordinate_position AND groups.EndSequence >= ref.Ordinate_position AND groups.Parcel_ID = ref.Parcel_IDORDER BY ref.Parcel_ID, Ordinate_position;