Wednesday, January 12, 2011

Generating XML file from SQL SELECT statement

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.

1 comment:

  1. How do you output this to a file to the local folder?

    ReplyDelete