sql - 在 SQL 2008 中生成 KML

Question

我有一个具有以下结构的表格:

id, - 主键

最小纬度 - 正方形的边界点

最小经度 - 正方形的边界点maxlatitude - 正方形的边界点maxlongitude-正方形的边界点

cetnerLatitude - 正方形的中心centerLongtude - 正方形的中心

最小最大字段创建一个正方形，中心纬度/经度是正方形的中心点。

我想使用“xml路径”生成KML文件。生成的 KML 文件应类似于以下内容:

<?xml version="1.0" encoding="UTF-8"?>
 <kml xmlns="http://www.opengis.net/kml/2.2">  
 <Document>
<Placemark>
  <name>example</name>
  <description>
dafdafdsaf
  </description>
  <Point>
    <coordinates>102.594411,14.998518</coordinates>
  </Point>
  <Polygon>
    <extrude>1</extrude>
    <altitudeMode>relativeToGround</altitudeMode>
    <outerBoundaryIs>
      <LinearRing>
        <coordinates>
          -77.05788457660967,38.87253259892824,100
          -77.05465973756702,38.87291016281703,100
          -77.05315536854791,38.87053267794386,100
          -77.05788457660967,38.87253259892824,100
        </coordinates>
      </LinearRing>
    </outerBoundaryIs>
  </Polygon>
</Placemark>
</Document>
</kml>"""

我开始解决 xmlpath SQL 查询，到目前为止得到了以下结果:

declare @id int
set @id = 22438
declare @kml xml;

with XMLNAMESPACES(
        'http://www.opengis.net/gml' as gml,
        'http://www.georss.org/georss' as georss
)

select @kml = 
        (select some as id , 

        cast([cent_latt] as varchar) + ', ' + cast([cent_long] as varchar) as Point
        from mytable
        Where some = @id

        for xml path('Placemark'), root('Document')
        )

select @kml

任何人都可以帮助生成如上所述的确切文件吗？
或者有没有办法使用 xslt 来达到同样的目的？

Answer 1

我无法真正理解哪些列名代表哪些信息，因此我创建了一个示例表，它将向您展示如何将不同部分放入 XML 中。

declare @T table
(
  Name varchar(50),
  Description varchar(50),
  Point varchar(50),
  Extrude int,
  AltitudeMode varchar(50),
  Coordinates varchar(200)
);

insert into @T values
('example', 'dafdafdsaf', '102.594411,14.998518', 1, 'relativeToGround', 
'-77.05788457660967,38.87253259892824,100
-77.05465973756702,38.87291016281703,100
-77.05315536854791,38.87053267794386,100
-77.05788457660967,38.87253259892824,100');

with xmlnamespaces(default 'http://www.opengis.net/kml/2.2')
select (
       select Name as name,
              Description as description,
              Point as 'Point/coordinates',
              (
                select Extrude as extrude,
                       AltitudeMode as altitudeMode,
                       Coordinates as 'outerBoundaryIs/LinearRing/coordinates'
                for xml path('Polygon'), type
              )
       from @T       
       for xml path('Placemark'), type
       )
for xml path('Document'), root('kml');

结果:

<kml xmlns="http://www.opengis.net/kml/2.2">
  <Document>
    <Placemark xmlns="http://www.opengis.net/kml/2.2">
      <name>example</name>
      <description>dafdafdsaf</description>
      <Point>
        <coordinates>102.594411,14.998518</coordinates>
      </Point>
      <Polygon xmlns="http://www.opengis.net/kml/2.2">
        <extrude>1</extrude>
        <altitudeMode>relativeToGround</altitudeMode>
        <outerBoundaryIs>
          <LinearRing>
            <coordinates>-77.05788457660967,38.87253259892824,100
-77.05465973756702,38.87291016281703,100
-77.05315536854791,38.87053267794386,100
-77.05788457660967,38.87253259892824,100</coordinates>
          </LinearRing>
        </outerBoundaryIs>
      </Polygon>
    </Placemark>
  </Document>
</kml>