xml remove xml namespaces appearing at certain elements

23 views Asked by At

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>
0

There are 0 answers