Using FOR XML clause to generate KML

  • Robert J. Green

    SSC Enthusiast

    Points: 112

    Comments posted to this topic are about the item Using FOR XML clause to generate KML

  • David McKinney

    SSChampion

    Points: 10358

    Firstly, I couldn't get the code to run from the article - as it's different from the code in the resource section!

    But more importantly I think you're missing the point about xml in SQL Server. With For Xml, Path, query. etc. you don't have to resort to string building to construct your xml documents. The code you've supplied could be rewritten much more simply and clearly. (At least, that's my initial feeling.)

    Sorry to be negative - hopefully you'll take it as an encouragement to explore the tools available.

    Regards,

    David McKinney.

  • feargal

    Valued Member

    Points: 57

    I have to agree with David.

    The simplest way to generate KML from SQL Server data is to serve it up using an xml template with an XSLT file.

    The idea of embedding elements in an sproc fills me with dread.

  • Guy Blackburn-525634

    SSC Rookie

    Points: 37

    I was recently trying to do the same thing in SQL Server 2k & came up with something like the following as an XML template file but saved with a kml extension:

    SELECT CONVERT(char(8),PP_Arrival_Date,14) AS 'name',

    PP_Diag_Desc AS 'description',

    Point.LongLat AS 'coordinates'

    FROM TodayByPostcode Placemark

    INNER JOIN Postcodes.dbo.[All Scotland Postcodes with LatLong] Point ON Placemark.PP_Postcode = Point.Postcode

    ORDER BY PP_Arrival_Date

    FOR XML auto, elements

    where Point.LongLat contains the Longitude Latitude pair of the centroid of each postcode & looks like: -3.0894, 58.2487

    and then I just open the URL to the KML file (e.g. http://myserver/inetpub/template/Test2.kml) in Google Earth from anywhere within my intranet to view an up to date set of points.

    I hope to develop this properly eventually, but I'm really still a beginner at all this so there may be much better ways of doing this !

  • Robert J. Green

    SSC Enthusiast

    Points: 112

    Thanks for the feedback.

    My goal was to provide one way to do generate KML, but over time I might find a more elegant way of exploiting the FOR XML clause. After submitting the article, I realized the string handling was unnecessary.

    Again, thanks for taking the time to try it out and make comments.

    Robert

  • alastair-804470

    Old Hand

    Points: 331

    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

  • Mike DiRenzo

    SSCrazy

    Points: 2013

    String maniputlation? Hmmm. While I value your article and would in no way fault your approach, there are much better alternatives - that are not too far off from where you are.

    Explorer this:

    for xml path ('application'), ROOT('applications')

    I think you will find that it is a better approach.

    -M

  • Robert J. Green

    SSC Enthusiast

    Points: 112

    Thanks for the feedback and corrections.

    I appreciate you taking the time to dig into this with such energy.

    I haven't had the pleasure to work with SQLServer 2008 yet, but look forward to learning more from this sage group of contributors.

  • Mike DiRenzo

    SSCrazy

    Points: 2013

    I have had from time to time the unfortunate experience of coming across too strongly. I have seen where others REALLY tend to bash folks. From what I have read in this thread (thus far), you are in good shape. No doubt, you are receiving quality feedback from folks who share the same kind of passion when it comes to XML and MSSQL server. Whats more, it is rather cool to have a contribution get published on an esteemed site such as this - so congrats.

    Now back to your contribution...refactor by exploring the alternatives that have been presented - especially mine - LOL. Although the string maniputation approach works, it is prone to errors and a tremendous amount of overhead not to mention maintainability.

    for xml path ('application'), ROOT('applications')

    For very simple XML document fragments, it works like a champ; for complex hierarchies, it is your very best friend indeed. An alternative, as it pertains to "shaping" XML output, requires many nulls and a bunch of unions and lends itself to yet another maintenance headache. The approach that I have described works in SQL 2005 and up.

    Good luck.

    -M

  • Scott Marquardt

    SSC Journeyman

    Points: 99

    This doesn't really work as KML needs to -- grouping folders under other folders. In this case, if I change the airport identifier so that it's identical for several placemarks, it just repeats them instead of grouping them.

    I have yet to find a single instance of someone who's using SQL to generate an n-level hierarchical KML file that works properly.

  • Robert J. Green

    SSC Enthusiast

    Points: 112

    Scott,

    Its nice that Google has the concept of hierarchical structure, but in practice I don't think it works for other means of generating KML either.

    Thanks for the note, I'll update the project soon.

    Robert

  • Scott Marquardt

    SSC Journeyman

    Points: 99

    Can't wait to see it.

    😉

    Seriously, though, it's amazing how little is out there to plagiarize to do this right.

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply