The other day I was struggling with generating XML data from a database I was working with. After talking with a co-worker, he mentioned using Transact-SQL and the “For XML Explicit” options related to a query. I managed to do what I needed, but it felt like I was making my way through the jungle with a small plastic knife, instead of a machete. So I looked at how to accomplish this task using the .NET Framework’s DataSet, DataTable and DataRelation classes.
I started Googling “export XML from DataSet” and found numerous examples of calling the “GetXml()” method in the DataSet class. But, virtually all of these discussions/ examples dealt with the situation in which the DataSet consisted of a single table. In the single-table case, GetXml() works as expected. But as soon as you add a second table, things get pretty messy pretty quickly. So I’ve created a tutorial on Generating Hierarchical Nested XML from a DataSet.
The trick to getting all this work, is to use DataRelations within a DataSet to tie the tables together in an organized fashion. Something many do already when needing to “walk” their way down (or up) a set of tables. But, even then, there are some tricks that you will need to be aware of.
The DataRelation class has a property named “Nested” which you will need to set, in order to get the “GetXml()” method to generate the nested XML that we’re after. But even, then there is still one more step we need to take.
For example, suppose we have a 3-table DataSet: Customer -> Order -> OrderItem (Parents pointing to children).
If we choose to set the “Nested” property in our DataRelations, we get XML in which multiple Order nodes are located under the Customer node. This is valid. However, many would prefer to have a single Orders node under the Customer node. The Order nodes would be children of the Orders node.
Similarly, there are many OrderItem nodes under each Order node. Here too, most would prefer a single OrderItems node under Order with each OrderItems containing the individual OrderItem nodes.
The solution to this is to create an additional “layer” in our DataSet. This “wrapper” layer serves no other purpose other than to help organize all our nodes. The challenge is that each layer is represented by a DataTable. And each of these new DataTables needs relations within our DataSet in order to generate the XML that we want.
Once these new tables and relations are in place, we start to see the XML that we want. A bit of a challenge at first and some extra work to accomplish what we need. But worth the effort in order to produce XML that is well organized.
A complete working example project is provided in the tutorial: Generating Hierarchical Nested XML from a DataSet.