Agree with David - although this article was only stated to cover FOR XML, you've totally wasted the opportunity to demonstrate some of the other XML features of SQL Server in favour of pretty ugly string manipulation instead.
There's a couple of mistakes as well:
- You refer to the KML reference documentation at Google (with a broken link to http://http//code.google.com/apis/kml/documentation/kmlreference.html), but the KML standard is administered by the OGC, and the official KML spec is defined at http://www.opengeospatial.org/standards/kml/. KML has nothing to do with Google (it was originally developed by a company that was purchased by Google, but that's it).
- The Point element is not a required element of placemark. Placemarks are features that have an associated geometry, and that geometry can just as readily be represented by the LineString or Polygon element as by a Point.
- You don't mention how to get the result of the query onto Google Earth (or Virtual Earth etc.). In practice this is normally achieved via a middle-tier web handler that connects to the database, retrieves the result of the query, and serves the result to a webpage with an embedded Google Earth control. If this is the case, some of the work such as the UTF-16 conversion is best handled by the middle-tier layer rather than in the backend database.
- The discussion is only really relevant to SQL Server 2005. For any users using SQL Server 2008 there are much better ways using the inbuilt spatial functionality, which can also be used for much more complicated geometry types than points with a single lat/long coordinate pair.
Beginning Spatial with SQL Server 2008. http://www.apress.com/book/view/1430218290