Total votes: 2
Print: Print Article
Please login to rate or to leave a comment.
Published: 10 Sep 2007
This article illustrates the translation to and from an ADO .NET DataSet and XML.
Since the arrival of the Internet, people have become more and more dependent on it for daily operations. The Web's (as
well as computing applications') insatiable appetite for data has grown even more intense. Most websites have seen the
interplay of relational database and XML. Websites rely on relational databases for storing a vast amount of data and
performing fast retrieval and manipulation. For exchanging data, they opt for protocols such as SOAP or REST (what strange
names!). In these cases, XML serves both as the foundation and the ultimate representation form. Let's briefly look at the
origin of XML and make a skim comparison between a relational database and the XML data model.
A brief history of XML
Relational database was born out of the ideas of an IBM mathematician and computer scientist, E. B. Codd, in the early
1970s. Since then it has become the "IT" player in the database industry. In comparison, XML is the new kid in town
and it is the result of a never-ending quest for a simpler yet more powerful language for data storage and communication.
In 1986, SGML or "Standard Generalized Markup Language" was issued as an international standard. It is a complex and
somewhat rigid language that requires expensive authoring tools. SGML was used extensively in large government, legal and
publishing industries. In 1990, a simpler and more flexible language called HTML was created and it soon became enormously
popular. However, its shortcomings also became increasingly visible. In 1996, the World Wide Web Consortium (W3C)
(underwritten by Sun and other outside organizations) formed a committee to explore ways of harnessing the strength of HTML
and SGML while sidestepping its limitations. The result is the creation of XML.
Relational Database and XML data model
A relational database consists of a collection of two-dimensional tables of rows and columns, interconnected by keys
shared across tables. Through relations (shared keys), tables can be joined, queried and rearranged in different ways. There
is no hierarchy or sequence to speak of.XML on the other hand is all about hierarchy and sequence, as it is mirrored in such
terms, parents, children and siblings (it might as well be superiors, subordinates and colleagues).
Since its debut, XML has wooed techies and laymen alike with its easiness and flexibility; and the fact that it is
human-readable. However, as much as XML has gained enormous momentum and widespread usage, relational database has long been
the backbone infrastructure of enterprise applications; and proven to be effective and efficient. In the foreseeable future,
relational database technology will remain the dominant technology, with major adoptions with respect to XML.
Many relational database vendors have provided extended database features to incorporate XML technology. For instance, SQL
server has XQuery support and has a new XML data type. Likewise, middleware vendors have also provided native support for
XML, like ADO.NET.
In ADO.NET, the central data object - the
DataSet - represents a hierarchical, disconnected data cache. Its
design is based on XML, which makes the translation between relational data and XML easy. The following discussion will use
some examples to illustrate the ways to map a
DataSet from and to a XML file.
Load a DataSet from XML
To load a
DataSet from an XML file, simply call the
DataSet.ReadXml method, like so:
Figure 1 shows a screenshot of the output:
Figure 1: Screenshot of the output
example, a flat XML file with no hierarchy or nested element is selected. As a result, the
DataSet contains only
one table and it can be conveniently bound to a
Loading Schema Information from XML
ReadXmlSchema or the
InferXmlSchema methods of the
DataSet allow you to load
DataSet schema information from an XML document. Alternatively, you may use the overloaded
XmlReadMode.InferSchema as the second argument.
DataSet Schema information can be written to an .xsd file using the
The following is the input XML file, books2.xml:
And here's the output file, books2.xsd:
The following figure shows a screenshot of the output table relationship:
Figure 2: Output table relationship
somewhat more complicated XML file with a hierarchy of 3 tiers has been chosen for the example. It is worth noting how the
relationship is constructed from the XML to
DataSet transformation. The hierarchies are automatically translated
into three tables and primary-foreign key pairs are inserted.
DataSet with a single table to XML
Want to map a
DataSet containing a single table to XML? Piece of cake. The following code illustrates how to
fork some information from an Access database into a table and write it to a XML file.
Here's a segment of the output XML file:
As you can see, the root element of the XML file takes the name of the
DataSet. In .NET 2.0, many
of the tasks that could previously only be performed by a
DataSet can also be tackled by a
DataTable; such as XML transformation.
DataSet with two tables in master-detail relationship
It gets a little complicated and code-heavy to map to an appropriately nested XML from a
master-detail tables. First, you need to specify the primary key of the master table. Second, you have to register the
foreign key of the detail table. Finally, you must set to
Nested property of the
The following is the output XML File:
DataSet with three or more tables to XML
While it is quite manageable and even fun to translate a two-master-detail-tabled
DataSet to an XML file, it
is quite hopeless to go beyond. I mean: If you want to rely only on the built-in methods of
DataSet and get the
XML file elegantly nested in multiple levels. Of course, it is a different story if you are willing to sweat and traverse
among the rows and columns of the different tables of a
While relational database remains the major database technology and crucial infrastructure of most business and web
applications, XML has shown clear advantages and great promises in data exchange and storage. Therefore, many database and
middleware vendors have taken strides to enable the integration and communication of both technologies. ADO .NET is no
exception. And the above article showed how easy it is to transform from XML to
DataSet and backwards.
Web developer, Data Analyst, GIS Programmer
This author has published 12 articles on DotNetSlackers. View other articles or the complete profile here.
Please login to rate or to leave a comment.