By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Shapes (
ID INT PRIMARY KEY IDENTITY(1,1),
Shape GEOMETRY
);
INSERT INTO Shapes (Shape) VALUES
(geometry::STGeomFromText('POLYGON ((565542.98375 2127263.4997410, 565538.48450 2127261.3187302, 565541.96658 2127254.1162, 565546.465835 2127256.297297, 565542.98375 2127263.4997410))',0))
,(geometry::STGeomFromText('POLYGON ((565547.281621307 2127097.9410014, 565549.457915 2127093.43948425, 565553.577449391 2127084.9189882, 565568.882475 2127092.31709055, 565562.586805441 2127105.3404182, 565547.281621307 2127097.9410014))',0))
;
2 rows affected
WITH NUMS AS
(
SELECT DISTINCT number as n
FROM master..[spt_values]
WHERE number between 1 and 128
)
SELECT ID, GeoPoint.STX AS X, GeoPoint.STY AS Y
FROM Shapes s
CROSS APPLY
(
SELECT n as PointN, Shape.STPointN(n) AS GeoPoint
FROM NUMS
WHERE n <= Shape.STNumPoints()
) ca;
| ID | X | Y |
|---|---|---|
| 1 | 565542.98375 | 2127263.499741 |
| 1 | 565538.4845 | 2127261.3187302 |
| 1 | 565541.96658 | 2127254.1162 |
| 1 | 565546.465835 | 2127256.297297 |
| 1 | 565542.98375 | 2127263.499741 |
| 2 | 565547.281621307 | 2127097.9410014 |
| 2 | 565549.457915 | 2127093.43948425 |
| 2 | 565553.577449391 | 2127084.9189882 |
| 2 | 565568.882475 | 2127092.31709055 |
| 2 | 565562.586805441 | 2127105.3404182 |
| 2 | 565547.281621307 | 2127097.9410014 |