Divide the coordinates into X and Y coordinates from a table having geometry data in SQL Server 2012

I have a table with a column Shape of geometry datatype.

This is the data in Shape :

POLYGON ((565542.98375 2127263.4997410, 565538.48450 2127261.3187302, 565541.96658 2127254.1162, 565546.465835 2127256.297297, 565542.9837 2127263.49974102))
POLYGON ((565547.281621307 2127097.9410014, 565549.457915 2127093.43948425, 565553.577449391 2127084.9189882, 565568.882475 2127092.31709055, 565562.586805441 2127105.3404182, 565547.2816807 2127097.94105044))

and so on....

I need output as

ID | X               | Y 
---+-----------------+-----------------
1  | 565542.98375    | 2127263.4997410
1  | 565538.48450    | 2127261.3187302 
1  | 565541.96658    | 2127254.1162 
1  | 565546.465835   | 2127256.297297 
1  | 565542.9837     | 2127263.49974102 
2  | 565547.281627   | 2127097.9410014 
2  | 565549.457915   | 2127093.43948425 
2  | 565553.5774391  | 2127084.9189882

and so on in table format

Solution

Here is an option which will work with 2012. Note that we maintain the sequence (RetSeq)

Example

SELECT A.ID
      ,B.RetSeq
      ,X = left(C.RetVal,charindex(' ',C.RetVal)-1)
      ,Y = substring(C.RetVal,charindex(' ',C.RetVal)+1,50)
 FROM  YourTable A
 Cross Apply (
        Select RetSeq = row_number() over (Order By 1/0)
              ,RetVal = B2.i.value('(./text())[1]', 'varchar(100)')
        From  (Select x = Cast('<x>' + replace(A.Shape.STAsText(),',','</x><x>')+'</x>' as xml)) as B1
        Cross Apply x.nodes('x') AS B2(i)
       ) B
  Cross Apply ( values (ltrim(rtrim(replace(replace(replace(B.RetVal,'POLYGON',''),'(',''),')','')))) ) C(RetVal)

Returns

ID  RetSeq  X                   Y
1   1       565542.98375        2127263.499741
1   2       565538.4845         2127261.3187302
1   3       565541.96658        2127254.1162
1   4       565546.465835       2127256.297297
1   5       565542.98375        2127263.499741
2   1       565547.281621307    2127097.9410014
2   2       565549.457915       2127093.43948425
2   3       565553.577449391    2127084.9189882
2   4       565568.882475       2127092.31709055
2   5       565562.586805441    2127105.3404182
2   6       565547.281621307    2127097.9410014

EDIT

Martin Smith's solution should really be the ACCEPTED answer. If you can't create a numbers table, you can use an ad-hoc tally table.

Example

Select A.ID 
      ,Seq = B.N
      ,X   = Shape.STPointN(N).STX
      ,Y   = Shape.STPointN(N).STY
 From  YourTable A
 Cross Apply (Select Top (Shape.STNumPoints()) N=Row_Number() Over (Order By 1/0) From master..spt_values n1, master..spt_values n2 ) B

Requested EDIT

;with cte as (
SELECT A.ID
      ,B.RetSeq
      ,X = left(C.RetVal,charindex(' ',C.RetVal)-1)
      ,Y = substring(C.RetVal,charindex(' ',C.RetVal)+1,50)
      ,Cnt = max(B.RetSeq) over (Partition by A.ID)
 FROM  YourTable A
 Cross Apply (
        Select RetSeq = row_number() over (Order By 1/0)
              ,RetVal = B2.i.value('(./text())[1]', 'varchar(100)')
        From  (Select x = Cast('<x>' + replace(A.Shape.STAsText(),',','</x><x>')+'</x>' as xml)) as B1
        Cross Apply x.nodes('x') AS B2(i)
       ) B
  Cross Apply ( values (ltrim(rtrim(replace(replace(replace(B.RetVal,'POLYGON',''),'(',''),')','')))) ) C(RetVal) 
) 
Select *
 From  cte
 Where RetSeq<Cnt
 Order By ID,RetSeq

OR ... Notice the minus 1 in the TOP

Select A.ID 
      ,Seq = B.N
      ,X   = Shape.STPointN(N).STX
      ,Y   = Shape.STPointN(N).STY
 From  YourTable A
 Cross Apply (Select Top (Shape.STNumPoints() - 1) N=Row_Number() Over (Order By 1/0) From master..spt_values n1, master..spt_values n2 ) B