Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using FOR XML clause to generate KML


Using FOR XML clause to generate KML

Author
Message
Robert J. Green
Robert J. Green
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 20
Comments posted to this topic are about the item Using FOR XML clause to generate KML
David McKinney
David McKinney
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1021 Visits: 2090
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
feargal
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 4
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
Guy Blackburn-525634
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 286
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
Robert J. Green
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 20
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
alastair-804470
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 105
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. www.apress.com/book/view/1430218290
Mike DiRenzo
Mike DiRenzo
SSC Veteran
SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)

Group: General Forum Members
Points: 213 Visits: 210
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
Robert J. Green
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 20
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
Mike DiRenzo
SSC Veteran
SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)

Group: General Forum Members
Points: 213 Visits: 210
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
Scott Marquardt
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 22
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search