locked
export data in sql server to xml file in format geoRss RRS feed

  • Question

  •  

    I try using SELECT ... FOR XML but don't work with spatial data and don't match format in geoRss .
    Friday, May 2, 2008 6:54 PM

Answers

  • try something like this. Remember the SQL Server is a Relational Database with XML and Spatial Features, not a Spatial Database... Smile

     

    Code Snippet

    with xmlnamespaces('http://www.georss.org/georss' as georss)

    select null as 'channel'

    , 'point example' as 'channel/title'

    , 'this is a point example' as 'channel/description'

    , CAST(centroid.STY as varchar(15))+','+CAST(centroid.STX as varchar(15)) +','+ CAST(centroid.STY as varchar(15))+','+CAST(centroid.STX as varchar(15)) as 'channel/georss:box'

    , 'GeoRSS-SimplePoint' as 'channel/item/title'

    , 'simple pont' as 'channel/item/description'

    , NEWID() as 'channel/item/guid'

    , 'http://georss.org/example/simple/point' as 'channel/item/link'

    , 'ktegels@develop.com' as 'channel/item/author'

    , cast(GetUTCDate() as DateTimeOffset) as 'channel/item/pubDate'

    , CAST(centroid.STY as varchar(15))+' '+CAST(centroid.STX as varchar(15)) as 'channel/item/georss:point'

    from censusData.zctas

    where zcta='57110'

    for xml path(''),root('rss'),type

     

     

     

    Saturday, May 3, 2008 3:38 AM
    Answerer
  • You can use a combination of SELECT... FOR XML and xquery to create the elements of a valid GeoRSS feed - discussed in more detail here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3100794&SiteID=1
    Sunday, May 4, 2008 8:45 PM
    Answerer

All replies

  • try something like this. Remember the SQL Server is a Relational Database with XML and Spatial Features, not a Spatial Database... Smile

     

    Code Snippet

    with xmlnamespaces('http://www.georss.org/georss' as georss)

    select null as 'channel'

    , 'point example' as 'channel/title'

    , 'this is a point example' as 'channel/description'

    , CAST(centroid.STY as varchar(15))+','+CAST(centroid.STX as varchar(15)) +','+ CAST(centroid.STY as varchar(15))+','+CAST(centroid.STX as varchar(15)) as 'channel/georss:box'

    , 'GeoRSS-SimplePoint' as 'channel/item/title'

    , 'simple pont' as 'channel/item/description'

    , NEWID() as 'channel/item/guid'

    , 'http://georss.org/example/simple/point' as 'channel/item/link'

    , 'ktegels@develop.com' as 'channel/item/author'

    , cast(GetUTCDate() as DateTimeOffset) as 'channel/item/pubDate'

    , CAST(centroid.STY as varchar(15))+' '+CAST(centroid.STX as varchar(15)) as 'channel/item/georss:point'

    from censusData.zctas

    where zcta='57110'

    for xml path(''),root('rss'),type

     

     

     

    Saturday, May 3, 2008 3:38 AM
    Answerer
  • You can use a combination of SELECT... FOR XML and xquery to create the elements of a valid GeoRSS feed - discussed in more detail here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3100794&SiteID=1
    Sunday, May 4, 2008 8:45 PM
    Answerer