Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Using FOR XML clause to generate KML Expand / Collapse
Author
Message
Posted Saturday, March 28, 2009 4:04 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, February 18, 2013 9:44 AM
Points: 10, Visits: 20
Comments posted to this topic are about the item Using FOR XML clause to generate KML
Post #685641
Posted Monday, March 30, 2009 12:49 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, November 24, 2014 12:40 AM
Points: 652, Visits: 1,894
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.
Post #685877
Posted Monday, March 30, 2009 2:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 30, 2009 3:08 PM
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.
Post #685915
Posted Monday, March 30, 2009 3:28 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 24, 2014 1:45 AM
Points: 5, Visits: 204
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 !
Post #685930
Posted Monday, March 30, 2009 4:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, February 18, 2013 9:44 AM
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

Post #685948
Posted Monday, March 30, 2009 10:45 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 1, 2012 10:31 AM
Points: 17, 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
Post #686305
Posted Monday, March 30, 2009 12:48 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 25, 2014 8:49 AM
Points: 147, Visits: 196
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
Post #686397
Posted Monday, March 30, 2009 8:18 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, February 18, 2013 9:44 AM
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.
Post #686698
Posted Monday, March 30, 2009 9:20 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 25, 2014 8:49 AM
Points: 147, Visits: 196
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
Post #686713
Posted Tuesday, October 19, 2010 7:28 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 12, 2012 3:00 PM
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.
Post #1007412
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse