Welp, I went and figured out how to export to WXR via sql for xml path. It's not pretty, and it's missing a couple of things like categories (I didn't feel like figuring out the subquery with a many-to-many relationship, and don't care about categories that much)
But it does grab all of your content and comments and packages it up for importing into wordpress. Here's what I came up with:
with xmlnamespaces
(
)
select Title as 'title',
Host as 'link',
SubTitle as 'description',
Language as 'language',
'1.0' as 'wp:wxr_version',
(
select Title as 'title',
DateAdded as 'pubDate',
Email as 'dc:creator',
Text as 'content:encoded',
ID as 'wp:post_id',
EntryName as 'wp:post_name',
'publish' as 'wp:status',
'post' as 'wp:post_type',
DateSyndicated as 'wp:post_date',
(
select Id as 'wp:comment_id',
Author as 'wp:comment_author',
Email as 'wp:comment_author_email',
IpAddress as 'wp:comment_author_IP',
DateCreated as 'wp:comment_date',
Body as 'wp:comment_content'
from subtext_FeedBack
where FeedbackType = 1 and EntryId = content.ID
for xml path('wp:comment'), type
)
from subtext_Content as content
where PostType = 1 and PostConfig <> 6
for xml path('item'), type
)
from subtext_Config
where BlogId = 1
for xml path('channel'), root('rss')
---
To save it as xml, I just clicked the result grid in SSMS and did a save from there. A couple of notes:
- you may have to manually add the version="2.0" attribute into the root rss node, I did just to be safe. I imported nearly 400 posts this way.
- this script only grabs published posts. it may require some kind of CASE/IF logic to do drafts or non-public posts.
- sql for xml is pretty rad.
I may also blog the solution, but since this thread is what comes up when you search for "export subtext wxr" I figured I'd put it here first.
Cheers!