title: SQL query to XML for KML Google Earth
body: <p>I am trying to make a KML file using SQL for XML to path.  But I need another "root" section named "Folder" below KML.  Everything I try makes another 
  section named folder for every sub-section.</p>
  
  <pre><code>with xmlnamespaces ( 
  'http://www.w3.org/2005/Atom' as Atom,
  'http://www.opengis.net/kml/2.2' as kml,
  'http://www.google.com/kml/ext/2.2' as gx,
  default 'http://www.opengis.net/kml/2.2'
  ) 
  
  select --'kml' as kml,
      --'NewSpots 2013' as 'Folder/Name',
      --Waypoint as 'Folder/Placemark/name',
      --Description as 'Folder/Placemark/description',
      --ProperDecimalGPS as 'Folder/Placemark/Point/coordinates'
      Waypoint as 'Placemark/name',
      Description as 'Placemark/description',
      ProperDecimalGPS as 'Placemark/Point/coordinates'
      --WPGroup as 'Folder/Name'
  from dbo.ttt
  where PortArea = 'NewSpots 2013'
  
  for xml path(''), root('kml')
  </code></pre>
  
  <p>Here is the output.  I need the sections as they show in reults below, but those items are commented out to easily see what I am missing/needing.</p>
  
  <pre><code>&lt;kml xmlns="http://www.opengis.net/kml/2.2" 
  
  xmlns:gx="http://www.google.com/kml/ext/2.2" xmlns:kml="http://www.opengis.net/kml/2.2" xmlns:Atom="http://www.w3.org/2005/Atom"&gt;
   &lt;!--&lt;Folder&gt;&lt;Name&gt;NewSpots 2013&lt;/Name&gt;--&gt;
    &lt;Placemark&gt;
      &lt;name&gt;106 Rok-1&lt;/name&gt;
      &lt;Point&gt;
        &lt;coordinates&gt;-94.271833,28.633483,0&lt;/coordinates&gt;
      &lt;/Point&gt;
    &lt;/Placemark&gt;
    &lt;Placemark&gt;
      &lt;name&gt;106 Rok-2&lt;/name&gt;
      &lt;Point&gt;
        &lt;coordinates&gt;-94.269783,28.649767,0&lt;/coordinates&gt;
      &lt;/Point&gt;
    &lt;/Placemark&gt;
    &lt;Placemark&gt;
      &lt;name&gt;106 Rok-3&lt;/name&gt;
      &lt;Point&gt;
        &lt;coordinates&gt;-94.269033,28.6336,0&lt;/coordinates&gt;
      &lt;/Point&gt;
    &lt;/Placemark&gt;
   &lt;!--&lt;/Folder&gt;--&gt;
  &lt;/kml&gt;
  </code></pre>
  
  <p>If you need the design of ttt table...</p>
  
  <pre><code>USE [Fishing]
  GO
  
  SET ANSI_NULLS ON
  GO
  
  SET QUOTED_IDENTIFIER ON
  GO
  
  SET ANSI_PADDING ON
  GO
  
  CREATE TABLE [dbo].[ttt](
      [PortArea] [varchar](50) NULL,
      [WPGroup] [varchar](150) NULL,
      [Waypoint] [varchar](150) NULL,
      [ProperDecimalGPS] [varchar](50) NULL,
      [Description] [varchar](max) NULL
  ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
  
  GO
  
  SET ANSI_PADDING OFF
  GO
  </code></pre>
  
  <p>Some Sample data :</p>
  
  <p><img src="https://i.stack.imgur.com/XzFoP.jpg" alt="Sample Data"></p>