How to read data from SQL database and store it into an XML file ?

Question

I have a Silverlight application that reads its content from an XML file. User can enter data and It'll be stored in SQL database. How can I read the data from SQL database and store it into an XML file ?

Thanks,

Answer 1

One easy way (presuming you are using SQL Server) is to append FOR XML AUTO on the end of your query that retrieves the data. This then returns the result set as an XML file. For example, taking the Northwind database as an example, you could use this query:

SELECT * FROM Products as P 
INNER JOIN Categories as C 
ON P.CategoryID = C.CategoryID 
FOR XML AUTO

This would generate the following XML:

<P ProductID="1" ProductName="Chai" SupplierID="1" CategoryID="1" QuantityPerUnit="10 boxes x 20 bags" UnitPrice="18.0000" UnitsInStock="39" UnitsOnOrder="0" ReorderLevel="10" Discontinued="0">
  <C CategoryID="1" CategoryName="Beverages" Description="Soft drinks, coffees, teas, beers, and ales" />
</P>
<P ProductID="2" ProductName="Chang" SupplierID="1" CategoryID="1" QuantityPerUnit="24 - 12 oz bottles" UnitPrice="19.0000" UnitsInStock="17" UnitsOnOrder="40" ReorderLevel="25" Discontinued="0">
  <C CategoryID="1" CategoryName="Beverages" Description="Soft drinks, coffees, teas, beers, and ales" />
</P>

See Retrieving Data as XML from SQL Server for more information.

To retrieve your data from SQL Server you will need to use ADO.NET. This is too big a topic to go into detail here, so I suggest reading a tutorial. However, the basic premise is you query the database and return your data as XML and then store this in a string. Once in a string you can write this to a file.