I needed to generate an OPML (XML) file from data from a SQL Server 2008 SELECT statement. It's a bit of a hack job, but this is pretty much what I did:
/* Generate part of the OPML file from the database */
/* Make sure max characters is 8192 */
/* Send results to text or to file */
/* Need to manually merge the rows back together */
SELECT
'<?xml version="1.0" encoding="utf-8"?><opml><head />'
+ (SELECT [col1] as text
,[col2] as title
,[col3] as type
,[col4] as xmlUrl
,[col5] as htmlUrl
FROM [mydb1].[dbo].[mytable1] t1
inner join [mydb1].[dbo].[mytable2] t2 on t1.id = t2.id
for xml raw ('outline'), root ('body'))
+ '</opml>'
Reference: Constructing XML Using FOR XML.
How do you output this to a file to the local folder?
ReplyDelete