i am trying to produce a kml file from sql server.This code is a small representation of my data. with xml namespaces, namespaces appear in subquery-Placemark section. i dont want that.
here is my query
declare @data table(name nvarchar(100), descr nvarchar(100),loc geography)
insert into @data(name,descr,loc)
select 'New York City','USA',geography::Point(40,40,4326)
union all
select 'Boston','USA',geography::Point(50,50,4326)
;with xmlnamespaces(default 'http://www.opengis.net/kml/2.2')
select CONCAT('kml_',format(getdate(),'yyyy_MM_dd')) [name]
,'icon-1644-000000-labelson' [Style/@id]
,'f000000' [Style/color]
,1 [Style/scale]
,'https://www.gstatic.com/mapspro/images/stock/503-wht-blank_maps.png' [Style/Icon/href]
,(select d.name [name]
,d.descr [description]
,'#icon-1644-4E342E-labelson' [styleUrl]
,concat(d.loc.Long,',',d.loc.Lat,',0')[Point/coordinates]
from @data d
and result is like
<kml xmlns="http://www.opengis.net/kml/2.2">
<Document>
<name>kml_2024_01_21</name>
<Style id="icon-1644-000000-labelson">
<color>f000000</color>
<scale>1</scale>
<Icon>
<href>https://www.gstatic.com/mapspro/images/stock/503-wht-blank_maps.png</href>
</Icon>
</Style>
<Placemark xmlns="http://www.opengis.net/kml/2.2">
<name>New York City</name>
<description>USA</description>
<styleUrl>#icon-1644-4E342E-labelson</styleUrl>
<Point>
<coordinates>40,40,0</coordinates>
</Point>
</Placemark>
<Placemark xmlns="http://www.opengis.net/kml/2.2">
<name>Boston</name>
<description>USA</description>
<styleUrl>#icon-1644-4E342E-labelson</styleUrl>
<Point>
<coordinates>50,50,0</coordinates>
</Point>
</Placemark>
</Document>
</kml>
i wanto remove namespaces at the Placemark section
Desired output:(no namespace after Placemark) How can I do it?
<Document>
<name>kml_2024_01_21</name>
<Style id="icon-1644-000000-labelson">
<color>f000000</color>
<scale>1</scale>
<Icon>
<href>https://www.gstatic.com/mapspro/images/stock/503-wht-blank_maps.png</href>
</Icon>
</Style>
<Placemark>
<name>New York City</name>
<description>USA</description>
<styleUrl>#icon-1644-4E342E-labelson</styleUrl>
<Point>
<coordinates>40,40,0</coordinates>
</Point>
</Placemark>
<Placemark>
<name>Boston</name>
<description>USA</description>
<styleUrl>#icon-1644-4E342E-labelson</styleUrl>
<Point>
<coordinates>50,50,0</coordinates>
</Point>
</Placemark>
</Document>
</kml>