Creating KML in SQL 2008

Question

I have a table with the following structure:

id, - Primary key

minlatitude - the end point of the square

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

cetnerLatitude - sqaure centerLonigtude Center - Hall Center

The min max fields create a square, and the center Lat / Long - for the center point of the square.

I want to generate a KML file using the "xml path". The generated KML file should look like this:

<?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 with xmlpath SQL query and still 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 someone help create the exact file as above?
Or is there a way to use xslt for the same thing?

Answer 1

I could not understand what column names you have for what information, so I created a sample table that will show you how you can get the different parts in 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>