Using FOR XML clause to generate KML

Question

Using FOR XML clause to generate KML

Robert J. Green

SSC Enthusiast

Points: 112
More actions
March 28, 2009 at 4:04 pm

#219484

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

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

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

David McKinney SSChampion Points: 10472 More actions · Answer 1

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 More actions · Answer 2

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 More actions · Answer 3

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:

SELECTCONVERT(char(8),PP_Arrival_Date,14)AS 'name',

PP_Diag_DescAS 'description',

Point.LongLatAS 'coordinates'

FROMTodayByPostcode 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 More actions · Answer 4

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 More actions · Answer 5

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 More actions · Answer 6

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 More actions · Answer 7

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 More actions · Answer 8

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 More actions · Answer 9

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 More actions · Answer 10

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 More actions · Answer 11

Can't wait to see it.

😉

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