SQL query to XML for KML Google Earth

Question

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.

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')

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.

<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">
 <!--<Folder><Name>NewSpots 2013</Name>-->
  <Placemark>
    <name>106 Rok-1</name>
    <Point>
      <coordinates>-94.271833,28.633483,0</coordinates>
    </Point>
  </Placemark>
  <Placemark>
    <name>106 Rok-2</name>
    <Point>
      <coordinates>-94.269783,28.649767,0</coordinates>
    </Point>
  </Placemark>
  <Placemark>
    <name>106 Rok-3</name>
    <Point>
      <coordinates>-94.269033,28.6336,0</coordinates>
    </Point>
  </Placemark>
 <!--</Folder>-->
</kml>

If you need the design of ttt table...

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

Some Sample data :

Sample Data

Shnugo · Accepted Answer · 2018-01-31T17:34:33.207

Try it like this

Short explanation: It would be possible, to pack all-in-one, but in this case you'd see the namespace declarations repeatedly (each Placemark would be a *sub-select). This is not wrong but annoying (see this related answer)

My solution: Create the XML in two steps and only the last one with namespaces

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
INSERT INTO dbo.ttt VALUES
 ('NewSpots 2013','106 Rok','106 Rok-1','-94.271833,28.633483,0',NULL)
,('NewSpots 2013','106 Rok','106 Rok-2','-94.269783,28.649767,0',NULL)
,('NewSpots 2013','106 Rok','106 Rok-3','-94.269033,28.6336,0',NULL);
GO

DECLARE @FolderName VARCHAR(MAX)='NewSpots 2013';

DECLARE @Placemarks XML;

SELECT @Placemarks=
(
    select Waypoint as 'name',
           Description as 'description',
           ProperDecimalGPS as 'Point/coordinates'
    from dbo.ttt
    where PortArea = @FolderName
    for xml path('Placemark'),TYPE
);

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 @FolderName AS 'Name'
      ,@Placemarks
FOR XML PATH('Folder'),ROOT('kml') 

GO
--Clean-up
--DROP TABLE dbo.ttt;

The result

<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">
  <Folder>
    <Name>NewSpots 2013</Name>
    <Placemark xmlns="">
      <name>106 Rok-1</name>
      <Point>
        <coordinates>-94.271833,28.633483,0</coordinates>
      </Point>
    </Placemark>
    <Placemark xmlns="">
      <name>106 Rok-2</name>
      <Point>
        <coordinates>-94.269783,28.649767,0</coordinates>
      </Point>
    </Placemark>
    <Placemark xmlns="">
      <name>106 Rok-3</name>
      <Point>
        <coordinates>-94.269033,28.6336,0</coordinates>
      </Point>
    </Placemark>
  </Folder>
</kml>

UPDATE: This approach is not correct!

Due to xmlns="" in the nested elements this elements are not living in the same default namespace!

The only solution seems to be a cast to nvarchar(max), then a REPLACE() to get rid of these xmlns="" declarations and then a re-cast to XML. I hope that this will be solved in a future version!

SQL query to XML for KML Google Earth

1 Answers1

UPDATE: This approach is not correct!