Introduction

Data Structure + Base Function

DECLARE
  @v_array XML;  -- XML is the array so we declare an array variable in this way

-- We implement a base function which all other "visible" functions will access
-- This base function enables arrays of geometry and geography
-- Four methods are implemented:
--   * Select - Select a value at the nominated position within the array
--   * Insert - Insert a geometry at the nominated position within the array
--   * Update - Update or replace the geometry at the nominated position within the array
--   * Delete - Delete the geometry at the nominated position within the array
--
CREATE FUNCTION [dbo].[_STArray]
( 
  @p_array  xml,                        -- The "Array"
  @p_i      int            = 1,         -- Index into the array
  @p_action varchar(10)    = 'select',  -- Select, Insert, Update, Delete
  @p_wkb    varbinary(max) = null,      -- Geometry Data - Ensures can save geometry and geography
  @p_srid   int            = 0          -- Needed because wkb does not contain SRID
)
Returns xml
...

-- And a public array element count function.
CREATE FUNCTION [dbo].[STNumArray]
( 
  @p_array xml
)
Returns int

Accessors and Setters

Four functions are declared for processing geometry or geography data within the array. These are “wrappers” over _STArray.

-- Geography
CREATE FUNCTION [dbo].[STGeogArray]
( 
  @p_array  xml,
  @p_i      int         = 1,
  @p_action varchar(10) = 'insert',
  @p_geog   geography 
)
Returns xml
...

CREATE FUNCTION [dbo].[STGeogArrayN]
( 
  @p_array xml,
  @p_i     int = 1
)
Returns geography
....

-- Geometry
CREATE FUNCTION [dbo].[STGeomArray]
( 
  @p_array  xml,
  @p_i      int         = 1,
  @p_action varchar(10) = 'insert',
  @p_geom   geometry
)
Returns xml
...

CREATE FUNCTION [dbo].[STGeomArrayN]
( 
  @p_array xml,
  @p_i     int = 1
)
Returns geometry
....

Examples

Declare @array xml
select ‘Empty Array’ as test, [dbo].[STNumArray](@array) as arraySize;

test arraySize
Empty Array 0

Set @array = [dbo].[_STArray](@array,1,’insert’,geometry::Point(1,2,0).STAsBinary(),0);
select ‘Insert into Empty ‘ as test, @array as array;

test array
Insert into Empty

Set @array = [dbo].[_STArray](@array,2,’insert’,geometry::Point(2,2,0).STAsBinary(),0);
select ‘Insert at position 2 (end)’ as test, @array as array;

test array
Insert at position 2 (end)

Set @array = [dbo].[_STArray](@array,0,’insert’,geometry::Point(3,3,0).STAsBinary(),0);
select ‘Insert at beginning ‘ as test, @array as array;

test array
Insert at beginning

Set @array = [dbo].[_STArray](@array,0,’update’,geometry::Point(4,4,0).STAsBinary(),0);
select ‘Update first geometry’ as test, @array as array;

test array
Update first geometry

Set @array = [dbo].[_STArray](@array,0,’delete’,NULL,0);
select ‘Delete first geometry’ as test, @array as array;

test array
Delete first geometry

Set @array = [dbo].[_STArray](@array,-1,’delete’,NULL,0);
select ‘Delete last geometry ‘ as test, @array as array;
GO

test array
Delete last geometry

Declare @v_geomXML xml;
Declare @v_vcWKB   varchar(max);
Declare @v_WKB     varbinary(max);
Declare @v_geom    geometry;
Declare @v_srid    int;
Set @v_geomXML = [dbo].[_STArray](@array,1,'select',NULL,0);
Set @v_vcWKB   = @v_geomXML.value('(/Geometry)[1]','varchar(max)');
Set @v_srid    = @v_geomXML.value('(/Geometry/@srid)[1]','int');
Set @v_geom    = geometry::STGeomFromWKB(CAST(@v_vcWKB AS xml).value('xs:base64Binary(sql:variable("@v_vcWKB"))', 'varbinary(max)'),0);
select 'Select First Geometry ' as test, @v_geomXML, @v_vcWKB, @v_geom.AsTextZM(), @v_geom.STSrid;

test                  (No column name)                                           (No column name)             (No column name) (No column name)
Select First Geometry <Geometry srid="1">AQEAAAAAAAAAAADwPwAAAAAAAABA</Geometry> AQEAAAAAAAAAAADwPwAAAAAAAABA POINT (1 2)      0

select 'Size' as test, [dbo].[STNumArray](@array) as arraySize;

test arraySize
Size 1

I hope this is of interest to someone.