#sql #sql-server #xml #tsql #xquery
#sql #sql-сервер #xml #tsql #xquery
Вопрос:
PointNum Атрибут означает количество координат в цепочке. Цепочка координат начинается с 1 и заканчивается также на 1 (внутри каждого Parcel элемента).
Однако в последовательностях этих точек есть еще одна внутренняя логика, которая должна быть объяснена. Это логика полного совпадения координат X и Y. Например:
-
в последовательности внутри Parcel_ID = 1 есть одинаковые координаты с числом точек 1 и 4, 5 и 7
-
в последовательности внутри Parcel_ID = 2 есть равные координаты с числом точек 1 и 3
ЦЕЛЬ: мне также нужно пронумеровать эти подсети. (в SequenceNum столбце)
код
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);
Желаемый результат (столбец «SequenceNum»)
----------- ---------------------- ------------ ------------ --------------------------
| 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 |
----------- ---------------------- ------------ ------------ --------------------------
Ответ №1:
Это может вам помочь. Пожалуйста, обратите внимание, что это работает для вашего примера, но я вижу, что это не работает для таких случаев, как…
- Более двух равных координат.
- Переплетенные группы координат.
- Переходы атрибута PointNum (разрывы в порядковой последовательности)
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;
Комментарии:
1. Спасибо! Но как в этом коде сохранить вид конструкции :
WITH XMLNAMESPACES (DEFAULT 'urn://some-namespace') INSERT INTO [DB].[dbo].[ThisTableName] (column1, column2, etc) SELECT… Я не могу понять это (в исходном коде я мог бы изменить код, чтобы сохранить эту конструкцию). Это необходимо, потому что мне приходится загружать много таблиц одновременно. Здесь я могу интегрироватьINSERT INTOпослеHAVING COUNT (*)> 1, но безNAMESPACE