convert AsGML() into Georss

Question

Hi I saw this post. i need the same thing but its urgent if someone can give me a basic structure i can modify it for my case.

http://social.msdn.microsoft.com/Forums/en-US/sqlspatial/thread/0918974c-0b8a-4637-937a-995ef3356ba8 

Just did not know how to use some of the contructs like for loop inside a stroed procedure to do the following thing

 

for $child in /where

if (fn:local-name($child) = "Point")
        then  <gml:Point> { $child/* } </gml:Point>

else  if (fn:local-name($child) = "LineString")
        then  <gml:LineString> { $child/* } </gml:LineString>

etc.

etc.

but if i can see a sample of working code i can easily learn the xquery and its structures

 

Thanks

If you guys have sample code please post :)

---

Chamy07

Answer 1

I got this far :) 

SELECT @GeoRSS.query('<feed xmlns="http://www.opengis.net/gml" xmlns:georss="http://www.georss.org/georss" xmlns:gml="http://www.opengis.net/gml">
		
			{
				for $e in feed/entry
				return
				<entry>
				<title>{ $e/Title/text() }</title>
				<description>{ $e/Description/text() }</description>
				<georss:where>
				{
					for $child in $e/georss:where/*
					return 
					if(fn:local-name($child) = "Point")
					then <gml:Point>{$child/*} </gml:Point>
					else ()
										
				}
				</georss:where>	
				</entry>		
			}		
	</feed>
	') AS GeoRSSFeed

 But I am not seeing point is wrapped around gml:Point :(

Any experts want to help me?

Thanks

 

---

Chamy07

Answer 2

-- Create a table
CREATE TABLE PropertiesForSale (
 id int,
 address varchar(255),
 location geography,
 description varchar(max)
)
GO


-- Insert sample data
INSERT INTO PropertiesForSale VALUES
(1,
'Pilgrims Way, Chew Stoke, Somerset',
geography::Point(51.354940,-2.635765,4326),
'Grade II Listed former Rectory, with magnificent architectural features and stunning gardens.'
),
(2,
'Moulsford, Wallingford, Oxfordshire',
geography::Point(51.549963,-1.149013,4326),
'Situated on the River Thames, this period house features landscaped gardens extending up to 240ft, and private mooring.'
),
(3,
'Pantings Lane, Highclere, Newbury',
geography::Point(51.347206,-1.375828,4326),
'A newly developed 5-bedroom house on the edge of Highclere, with very high build specifications used throughout.'
)
GO


-- Create stored procedure to generate GeoRSS
CREATE PROCEDURE [dbo].[uspGeoRSSFeeder]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Declare an XML variable to hold the GeoRSS output
DECLARE @GeoRSS xml;

/**
 * Create the elements of the feed using SELECT … FOR XML and AsGml()
**/
WITH XMLNAMESPACES (
 'http://www.opengis.net/gml' AS gml,
 'http://www.georss.org/georss' AS georss
)
SELECT @GeoRSS =
 (SELECT
 [address] AS title,
 [description] AS description,
 location.AsGml() AS [georss:where]
 FROM
 PropertiesForSale
 FOR XML PATH('item'), ROOT('channel')
)

/**
 * Style the results using XQuery
 **/
SELECT @GeoRSS.query('
<rss version="2.0"
 xmlns:georss="http://www.georss.org/georss"
 xmlns:gml="http://www.opengis.net/gml">
<channel>
 <title>SQL Server GeoRSS Feed</title>
 <description>This feed contains information about some fictional properties for 
sale in order to demonstrate how to syndicate spatial data using the GeoRSS format. 
</description>
 <link>http://www.beginningspatial.com</link>
 {
 for $e in channel/item
 return
 <item>
 <title> { $e/title/text() }</title>
 <description> { $e/description/text() }</description>
 <georss:where>
  {
  for $child in $e/georss:where/*
  return
  if (fn:local-name($child) = "Point") then <gml:Point> { $child/* } </gml:Point>
  else if (fn:local-name($child) = "LineString") then <gml:LineString> { $child/* } </gml:LineString>
  else if (fn:local-name($child) = "Polygon") then <gml:Polygon> { $child/* } </gml:Polygon>
  else if (fn:local-name($child) = "MultiPoint") then <gml:MultiPoint> { $child/* } </gml:MultiPoint>
  else if (fn:local-name($child) = "MultiCurve") then <gml:MultiCurve> { $child/* } </gml:MultiCurve>
  else if (fn:local-name($child) = "MultiSurface") then <gml:MultiSurface> { $child/* } </gml:MultiSurface>
  else if (fn:local-name($child) = "MultiGeometry") then <gml:MultiGeometry> { $child/* } </gml:MultiGeometry>
  else ()
  }
 </georss:where>
 </item>
 }
</channel>
</rss>
') AS GeoRSSFeed
END
GO

-- Execute the stored procedure
EXEC uspGeoRSSFeeder;

---

twitter: @alastaira blog: http://alastaira.wordpress.com/

Answer 3

 

WITH XMLNAMESPACES ('http://www.opengis.net/gml' AS gml, 

 

'http://www.georss.org/georss' AS georss, 

 

DEFAULT 'http://www.opengis.net/gml' 

 

)

THis is what tripped me i used

WITH XMLNAMESPACES (
'http://www.opengis.net/gml' AS gml,
'http://www.georss.org/georss' AS georss
)
 

from your answer it worked. thanks

---

Chamy07

Answer 4

Hi I saw this post. i need the same thing but if someone help me for saving stored procedure result in to file(xml).