SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE SpatialTable   
    ( id int IDENTITY (1,1),  
    GeomCol1 geometry );  
INSERT INTO SpatialTable
SELECT geometry::STGeomFromText(
'POLYGON ((565542.98375 2127263.4997410, 565538.48450 2127261.3187302, 565541.96658 2127254.1162, 565546.465835 2127256.297297, 565542.98375 2127263.4997410))',0)
UNION ALL
SELECT 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

SELECT ID, s1.rn, s3.x, s3.y, GeomCol1
FROM SpatialTable s
CROSS APPLY (SELECT value, 
             ROW_NUMBER() OVER(ORDER BY 1/0) AS rn FROM STRING_SPLIT(s.GeomCol1.STAsText() ,','))
   s1
CROSS APPLY (SELECT TRIM(TRANSLATE(value, 'POLYGON()', '         '))) s2(r)
CROSS APPLY (SELECT TRY_CAST(LEFT(s2.r, CHARINDEX(' ',s2.r)) AS DECIMAL(18,6)),
                    TRY_CAST(RIGHT(s2.r,LEN(s2.r)-CHARINDEX(' ',s2.r)) AS DECIMAL(18,6))
            ) s3(x,y)


ID
rn
x
y
GeomCol1


1
1
565542.983750
2127263.499741
POLYGON ((565542.98375 2127263.499741, 565538.4845 2127261.3187302, 565541.96658 2127254.1162, 565546.465835 2127256.297297, 565542.98375 2127263.499741))

1
2
565538.484500
2127261.318730
POLYGON ((565542.98375 2127263.499741, 565538.4845 2127261.3187302, 565541.96658 2127254.1162, 565546.465835 2127256.297297, 565542.98375 2127263.499741))

1
3
565541.966580
2127254.116200
POLYGON ((565542.98375 2127263.499741, 565538.4845 2127261.3187302, 565541.96658 2127254.1162, 565546.465835 2127256.297297, 565542.98375 2127263.499741))

1
4
565546.465835
2127256.297297
POLYGON ((565542.98375 2127263.499741, 565538.4845 2127261.3187302, 565541.96658 2127254.1162, 565546.465835 2127256.297297, 565542.98375 2127263.499741))

1
5
565542.983750
2127263.499741
POLYGON ((565542.98375 2127263.499741, 565538.4845 2127261.3187302, 565541.96658 2127254.1162, 565546.465835 2127256.297297, 565542.98375 2127263.499741))

2
1
565547.281621
2127097.941001
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))

2
2
565549.457915
2127093.439484
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))

2
3
565553.577449
2127084.918988
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))

2
4
565568.882475
2127092.317091
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))

2
5
565562.586805
2127105.340418
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))

2
6
565547.281621
2127097.941001
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))

ID	rn	x	y	GeomCol1
1	1	565542.983750	2127263.499741	POLYGON ((565542.98375 2127263.499741, 565538.4845 2127261.3187302, 565541.96658 2127254.1162, 565546.465835 2127256.297297, 565542.98375 2127263.499741))
1	2	565538.484500	2127261.318730	POLYGON ((565542.98375 2127263.499741, 565538.4845 2127261.3187302, 565541.96658 2127254.1162, 565546.465835 2127256.297297, 565542.98375 2127263.499741))
1	3	565541.966580	2127254.116200	POLYGON ((565542.98375 2127263.499741, 565538.4845 2127261.3187302, 565541.96658 2127254.1162, 565546.465835 2127256.297297, 565542.98375 2127263.499741))
1	4	565546.465835	2127256.297297	POLYGON ((565542.98375 2127263.499741, 565538.4845 2127261.3187302, 565541.96658 2127254.1162, 565546.465835 2127256.297297, 565542.98375 2127263.499741))
1	5	565542.983750	2127263.499741	POLYGON ((565542.98375 2127263.499741, 565538.4845 2127261.3187302, 565541.96658 2127254.1162, 565546.465835 2127256.297297, 565542.98375 2127263.499741))
2	1	565547.281621	2127097.941001	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))
2	2	565549.457915	2127093.439484	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))
2	3	565553.577449	2127084.918988	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))
2	4	565568.882475	2127092.317091	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))
2	5	565562.586805	2127105.340418	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))
2	6	565547.281621	2127097.941001	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))