Store Relational database as XML

Question

I have to store relational data (text, numbers and pictures) into an XML file. I want to maintain the relationship between data and the tables structure.

How do I go about doing that?

My Windows Mobile application use Windows Communication Foundation (WCF) to get data. Once it gets the data it stores them into an XML file.

The WCF use ADO.NET Entity Framework to data retrieve.

I'm going to use SQL Server 2008.

The data will be use to show it on application. I don't want to use SQL Server CE to store data because it's slower. Maybe, instead of using XML file I can use text plain files to store texts and numbers, and image files to store images.

In short, XML will be a way to store the data instead using SQL Server CE or WCF.

Thank you!

Answer 1

What SQL Server are you using? Some servers allow you to request an XML response for a query. That could save you a lot of work.

EDIT: Since you have specified that you will use MSSQL 2008, you may be in luck. In MSSQL 2005 and later there is a "FOR XML" keyword which allows you to get results as XML. Give it a try.

Answer 2

What do you mean by, "maintain the relationship between data and the tables structure"? Are you talking about more than maintaining the ID of a source database record in an attribute of an XML node? What do you need to do with the XML data once you have it? Will it be edited and have to be re-applied to the database?

More questions than answers from me....sorry.

Answer 3

While it's a little ambiguous what is needed, you can try looking into 'FOR XML EXPLICIT' under SQL Server. This will include relationships. Link: http://www.eggheadcafe.com/articles/20030804.asp

Answer 4

what about a dataset ? define the tables and relations, load the data into the dataset, and save it as XML (WriteXml method)

Answer 5

Define your XML format as an XSD. If you use Visual Studio use add new DataSet and the "custom tool" will automagically create a strong-typed set of classes based on DataSet. You can define tables and relations with constraints. It is easy to do in Visual Studio. If you are Visual Studio averse, you can create the XSD by hand and use xsd.exe to generate your code.

Once you have the strong-typed DataSet filled with data you can serialize it to an XML stream by calling WriteXml.

Answer 6

Thanks for the additional detail.

My suggestion is first, try SQL Server CE. It might not actually be slower for what you're doing. The time you save in not reinventing the wheel might be better spent in speeding up other parts of your application.

For the rest, assuming you don't need to store the data for long, nor do much with it, I'd suggest copying the data from the Web Service return type into a DataSet. The DataSet type has a WriteXml method that can save the data, and a ReadXml method that can read it back in.