Create XML document using C# with SQL Server as datasource

Question

I need to create an xml feed from my SQL Server database

I have seen it done in many different ways like: http://www.primaryobjects.com/CMS/Article67.aspx

However that seems to be just wrong..

What is the best an easiest way to go about this??

I have a XSD document to work from BTW.

Answer 1

Personally I find that LINQ to SQL + LINQ to XML works really nicely, so long as the result is small enough to fit in memory comfortably (i.e. it's not as good for a streaming solution).

For instance, I have a single (very large) statement which converts items in my database into an RSS feed. It's basically a declarative approach, and it works well. It's something like this:

XDocument doc = new XDocument(
    new XDeclaration("1.0", "UTF-8", "yes"),
    new XElement("rss",
        new XAttribute("version", "2.0"),
        new XElement("channel",

            new { title="C# in Depth news",
                  link ="http://csharpindepth.com/News.aspx",
                  description = "C# in Depth news items",
                  language = "en-gb",
                  generator = "LINQ",
                  docs = "http://blogs.law.harvard.edu/tech/rss",
                  pubDate = DateTimeOffset.UtcNow.ToString
                      (Rfc822Format, CultureInfo.InvariantCulture),
                  lastBuiltDate = items.First().CreatedDate.ToString
                      (Rfc822Format, CultureInfo.InvariantCulture),
            }.AsXElements(),
            items.Select(item =>
                new XElement("item",
                    new { title=item.Title, 
                          link=string.Format(LinkFormat, item.NewsItemID), 
                          description=item.Summary,
                          author="[email protected]",
                          pubDate = item.CreatedDate.ToString
                              (Rfc822Format, CultureInfo.InvariantCulture)
                    }.AsXElements()
                )
            )
        )
    )
);

That uses a little extension method I've got to convert anonymous types into XElements - it's available in MiscUtil, and does the obvious thing.

(And yes, I should probably have a method to convert dates to Rfc822 format...)

Answer 2

For creating rss feeds i prefer http://www.rssdotnet.com/ , there classes work really well and since you are working with objects it makes generating your output quite easy if you have a DAL of some sort.

Answer 3

You could always output XML from the database directly and then manipulate accordingly to add the correct root node and attributes. This is very straightforward in SQL Server 2005 onwards.

This answer provides some detail on some of the options that you have.