Best method to populate XML from SQL query in ASP.NET?

I need to create a service that will return XML containing data from the database. So I am thinking about using an ASHX that will accept things like date range and POST an XML file back. I have dealt with pages pulling data from SQL Server and populating into a datagrid for visual display but never into XML for delivery, what is the best way to do this? Also if an ASHX and POST isn't the best method for delivery let me know... thanks!

EDIT: These answers are great and pointing me in the right direction. I should have also mentioned that the XML format has already been decided so I can't use any automatically generated one.

From stackoverflow

• Have you tried DataSet.WriteXml()?

  You could have this be the output of a web service call.

• you can obtained that to a datatable and then call myTable.WriteXML()

  if you are populating classes with the your database results then add the serializable attribute to the header your classes, and use the XMLSerializer

• Sql Server 2005 and above has a "FOR XML AUTO" command that will convert your recordset to XML for you. Then you just have to return a string from your ASHX.

• Beginning with SQL Server 2000, you can return quey results as XML. For absolute control of them, use the "FOR XML EXPLICIT" command. You can use any format you desire that way. http://msdn.microsoft.com/en-us/library/ms189068.aspx

  It's as easy as writing your result to the raw output then. For added points, you can return the result set to an XPathDocument, pass it through an XSL transformation, and send the results out in any format you choose (HTML vs XML at the click of a button perhaps).

• Combining linq2sqlwith the XElement classes, something along the lines:

  var xmlContacts =
        new XElement("contacts",
           (from c in context.Contacts
           select new XElement("contact",
              new XElement
              {
                 new XElement("name", c.Name), 
                 new XElement("phone", c.Phone),
                 new XElement("postal", c.Postal)
              )
           )
           ).ToArray()
        )
     );
  

  Linq2sql will retrieve the data in a single call to the db, and the processing of the XML will be done at the business server. This splits the load better, since you don't have the sql server doing all the job.

  shogun : I did a google search for linq2sql and all the results said, problems with... issues with... ...issues, and so forth, but I found no official site to download any library from, is this built into ASP.NET or what?

  eglasius : @Ryan it is part of .net 3.5, check this 9 part series: http://weblogs.asp.net/scottgu/archive/2007/09/07/linq-to-sql-part-9-using-a-custom-linq-expression-with-the-lt-asp-linqdatasource-gt-control.aspx --- also see: http://msdn.microsoft.com/en-us/library/bb386976.aspx

• Converting an automatically generated format into a specified one is a job for xslt. Just find a way to run the output from the tool through an xslt filter.

  Oracle has a great product for doing exactly this job - the oracle XDK. But it's a java thing, not ASP as far as I know.

  For an example, this XHTML http://www.anbg.gov.au/abrs/online-resources/flora/stddisplay.xsql?pnid=2524

  is generated automatically from this XML, which is generated by oracle http://www.anbg.gov.au/abrs/online-resources/flora/stddisplay.xsql?pnid=2524&xml-stylesheet=none

  Of course, you are not after XHTML, but some other XML format. But XSLT will do the job.