Database table to XML document?

Question

Basically I have a asp.net that I need to display an XML document (straight xml, with the tree nodes) I have the sql statement which returns the row names and each one of their values. Just wondering how I should go about doing this?

SQL Server 2008, my query is just a select * from offices, my results is "1","New York","New York City","555-5555" I would like the output to be

<item id="1"> 
<state>New York</state> 
<city>New York City</city> 
<phone>555-5555</phone> 
</item> 

where state/city/phone is the column name, and the value is the value of that column

Answer 1

You can pass by datatable than a XML

Answer 2

SELECT id AS '@id',state,city,phone FROM offices AS item
FOR XML PATH, ROOT('Offices')

Answer 3

You can select your query into a DataSet: (Taken from MSDN)

  string queryString = 
      "SELECT CustomerID, CompanyName FROM dbo.Customers";
    SqlDataAdapter adapter = new SqlDataAdapter(queryString, connection);

DataSet customers = new DataSet();
adapter.Fill(customers, "Customers");

Then, you can write that DataSet to an XML string:

string xmlDS = custDS.GetXml();

View the MSDN pages for more options.