CODEWITHSUNDEEP
c#asp.netsqlxml
Spooks
Spooks

Reputation: 7177

Database table to XML document?

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

Upvotes: 0

Views: 1563

Answers (3)

Jim Schubert
Jim Schubert

Reputation: 20357

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.

Upvotes: 3

Joakim
Joakim

Reputation: 2217

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

Upvotes: 1

ma7moud
ma7moud

Reputation: 423

You can pass by datatable than a XML

Upvotes: 0

Related Questions