title: Generate KML in SQL 2008
body: <p>I have a table with following structure :</p>
  
  <p>id, - Primary key</p>
  
  <p>minlatitude - boundary point of a square</p>
  
  <p>minlongitude - boundary point of a square
  maxlatitude - boundary point of a square
  maxlongitude- boundary point of a square</p>
  
  <p>cetnerLatitude - Center of a sqaure
  centerLonigtude - Center of a sqaure</p>
  
  <p>The min max fields creates a square and center  Lat/Long is for center point of the square.</p>
  
  <p>I want to generate KML file using " xml path ". The generated KML file should look similar to following:</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;
   &lt;kml xmlns="http://www.opengis.net/kml/2.2"&gt;  
   &lt;Document&gt;
  &lt;Placemark&gt;
    &lt;name&gt;example&lt;/name&gt;
    &lt;description&gt;
  dafdafdsaf
    &lt;/description&gt;
    &lt;Point&gt;
      &lt;coordinates&gt;102.594411,14.998518&lt;/coordinates&gt;
    &lt;/Point&gt;
    &lt;Polygon&gt;
      &lt;extrude&gt;1&lt;/extrude&gt;
      &lt;altitudeMode&gt;relativeToGround&lt;/altitudeMode&gt;
      &lt;outerBoundaryIs&gt;
        &lt;LinearRing&gt;
          &lt;coordinates&gt;
            -77.05788457660967,38.87253259892824,100
            -77.05465973756702,38.87291016281703,100
            -77.05315536854791,38.87053267794386,100
            -77.05788457660967,38.87253259892824,100
          &lt;/coordinates&gt;
        &lt;/LinearRing&gt;
      &lt;/outerBoundaryIs&gt;
    &lt;/Polygon&gt;
  &lt;/Placemark&gt;
  &lt;/Document&gt;
  &lt;/kml&gt;"""
  </code></pre>
  
  <p>I started working around the xmlpath SQL query and so far got the following result:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Can anyone help generating the exact file as mentioned above?<br>
  Or is there a way to use xslt for the same?</p>