SQL FOR XML issue [modified]

DerekFL

Im trying to write a SQL query to populate a XML data feed. I have the query working but they need nodes with prefixes like "media:description" and "live:publocation" I tried SELECT RTRIM(Title) AS Title, [Description], Url AS link, DateCreated AS pubDate, thumbnail AS 'media:thumbnail' FROM dbo.Videos FOR XML PATH('item'), ROOT('media')) but that does not work. Anyone done this before?

modified on Wednesday, July 9, 2008 2:06 PM

leoinfo · modified on Wednesday, July 9, 2008 2:06 PM

I'm not sure I understood what you need... Try this:

WITH XMLNAMESPACES ('uri1' as media, 'uri2' as live)
SELECT RTRIM(Title) AS 'media:title'
   , [Description]  AS 'media:description'
   , Url            AS 'live:link'
   , DateCreated    AS 'media:pubdate'
   , thumbnail      AS 'media:thumbnail' 
FROM dbo.Videos FOR XML PATH('item'), ROOT('root')