Steve B Steve B - 6 months ago 31
SQL Question

SQL query to XML for KML Google Earth

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

Answer

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>