Generate KML in SQL 2008

Question

I have a table with following structure :

id, - Primary key

minlatitude - boundary point of a square

minlongitude - boundary point of a square maxlatitude - boundary point of a square maxlongitude- boundary point of a square

cetnerLatitude - Center of a sqaure centerLonigtude - Center of a sqaure

The min max fields creates a square and center Lat/Long is for center point of the square.

I want to generate KML file using " xml path ". The generated KML file should look similar to following:

<?xml version="1.0" encoding="UTF-8"?>
 <kml xmlns="http://www.opengis.net/kml/2.2">  
 <Document>
<Placemark>
  <name>example</name>
  <description>
dafdafdsaf
  </description>
  <Point>
    <coordinates>102.594411,14.998518</coordinates>
  </Point>
  <Polygon>
    <extrude>1</extrude>
    <altitudeMode>relativeToGround</altitudeMode>
    <outerBoundaryIs>
      <LinearRing>
        <coordinates>
          -77.05788457660967,38.87253259892824,100
          -77.05465973756702,38.87291016281703,100
          -77.05315536854791,38.87053267794386,100
          -77.05788457660967,38.87253259892824,100
        </coordinates>
      </LinearRing>
    </outerBoundaryIs>
  </Polygon>
</Placemark>
</Document>
</kml>"""

I started working around the xmlpath SQL query and so far got the following result:

declare @id int
set @id = 22438
declare @kml xml;

with XMLNAMESPACES(
        'http://www.opengis.net/gml' as gml,
        'http://www.georss.org/georss' as georss
)

select @kml = 
        (select some as id , 

        cast([cent_latt] as varchar) + ', ' + cast([cent_long] as varchar) as Point
        from mytable
        Where some = @id

        for xml path('Placemark'), root('Document')
        )

select @kml

Can anyone help generating the exact file as mentioned above?
Or is there a way to use xslt for the same?

Answer 1

I could not really understand what columns names you have for what information so I created a sample table that will show you how you can get the different parts into the XML.

declare @T table
(
  Name varchar(50),
  Description varchar(50),
  Point varchar(50),
  Extrude int,
  AltitudeMode varchar(50),
  Coordinates varchar(200)
);

insert into @T values
('example', 'dafdafdsaf', '102.594411,14.998518', 1, 'relativeToGround', 
'-77.05788457660967,38.87253259892824,100
-77.05465973756702,38.87291016281703,100
-77.05315536854791,38.87053267794386,100
-77.05788457660967,38.87253259892824,100');

with xmlnamespaces(default 'http://www.opengis.net/kml/2.2')
select (
       select Name as name,
              Description as description,
              Point as 'Point/coordinates',
              (
                select Extrude as extrude,
                       AltitudeMode as altitudeMode,
                       Coordinates as 'outerBoundaryIs/LinearRing/coordinates'
                for xml path('Polygon'), type
              )
       from @T       
       for xml path('Placemark'), type
       )
for xml path('Document'), root('kml');

Result:

<kml xmlns="http://www.opengis.net/kml/2.2">
  <Document>
    <Placemark xmlns="http://www.opengis.net/kml/2.2">
      <name>example</name>
      <description>dafdafdsaf</description>
      <Point>
        <coordinates>102.594411,14.998518</coordinates>
      </Point>
      <Polygon xmlns="http://www.opengis.net/kml/2.2">
        <extrude>1</extrude>
        <altitudeMode>relativeToGround</altitudeMode>
        <outerBoundaryIs>
          <LinearRing>
            <coordinates>-77.05788457660967,38.87253259892824,100
-77.05465973756702,38.87291016281703,100
-77.05315536854791,38.87053267794386,100
-77.05788457660967,38.87253259892824,100</coordinates>
          </LinearRing>
        </outerBoundaryIs>
      </Polygon>
    </Placemark>
  </Document>
</kml>