There are two approaches to work with XML and ADO. First, you can use ADO.NET to access XML documents. Second, you can use XML and ADO.NET to access XML. Additionally, you can access a relational database using ADO.NET and XML.NET.

Reading XML using Data Set

In ADO.NET, you can access the data using the DataSet class. The DataSet class implements methods and properties to work with XML documents. The following sections discuss methods that read XML data.

The Read xml Method

ReadXml is an overloaded method; you can use it to read a data stream, TextReader, XmlReader, or an XML file and to store into a DataSet object, which can later be used to display the data in a tabular format. The ReadXml method has eight overloaded forms. It can read a text, string, stream, TextReader, XmlReader, and their combination formats. In the following example, create a new DataSet object.

In the following example, create a new DataSet object and call the DataSet. ReadXml method to load the books.xml file in a DataSet object:

        'Create a DataSet object
        Dim ds As New DataSet()
        ' Fill with the data
        ds.ReadXml("books.xml")

Once you've a DataSet object, you know how powerful it is. Make sure you provide the correct path of books.xml.

Note: Make sure you add a reference to System.Data and the System.Data.Common namespace before using DataSet and other common data components.

The ReadXmlSchema method

The ReadXMLSchema method reads an XML schema in a DataSet object. It has four overloaded forms. You can use a Text Reader, string, stream, and XmlReader. The following example shows how to use a file as direct input and call the ReadXmlSchema method to read the file:

        Dim ds As New DataSet()
        ds.ReadSchema("c:\books.xml")

The following example reads the file XmlReader and uses XmlTextReader as the input of ReadXmlSchema:

        'Create a dataset object
        Dim ds As New DataSet("New DataSet")
        ' Read xsl in an XmlTextReader
        Dim myXmlReader As New XmlTextReader("c:\books.Xml")
        ' Call Read xml schema
        ds.ReadXmlSchema(myXmlReader)
        myXmlReader.Close()

Writing XML using Data Set

Not only reading, the DataSet class contains methods to write XML file from a DataSet object and fill the data to the file.

The Writexml Method

The WriteXml method writes the current data (the schema and data) of a DataSet object to an XML file. This is overloaded method. By using this method, you can write data to a file, stream, TextWriter, or XmlWriter. This example creates a DataSet, fills the data for the DataSet, and writes the data to an XML file.

Listing 6-26. Write xml Method 

Imports System
Imports System.IO
Imports System.Xml
Imports System.Data

Namespace XmlAndDataSetsampB2
    Class XmlAndDataSetSampCls
        Public Shared Sub Main()
            Try
                ' Create a DataSet, namespace and Student table
                ' with Name and Address columns
                Dim ds As New DataSet("DS")
                ds.[Namespace] = "StdNamespace"
                Dim stdTable As New DataTable("Student")
                Dim col1 As New DataColumn("Name")
                Dim col2 As New DataColumn("Address")
                stdTable.Columns.Add(col1)
                stdTable.Columns.Add(col2)
                ds.Tables.Add(stdTable)

                'Add student Data to the table
                Dim newRow As DataRow
                newRow = stdTable.NewRow()
                newRow("Name") = "Mahesh Chand"
                newRow("Address") = "Meadowlake Dr, Dtown"
                stdTable.Rows.Add(newRow)
                newRow = stdTable.NewRow()
                newRow("Name") = "Mike Gold"
                newRow("Address") = "NewYork"
                stdTable.Rows.Add(newRow)
                newRow = stdTable.NewRow()
                newRow("Name") = "Mike Gold"
                newRow("Address") = "New York"
                stdTable.Rows.Add(newRow)
                ds.AcceptChanges()
 
                ' Create a new StreamWriter
                ' I'll save data in stdData.Xml file
                Dim myStreamWriter As New System.IO.StreamWriter("c:\stdData.xml")

                ' Writer data to DataSet which actually creates the file
                ds.WriteXml(myStreamWriter)
                myStreamWriter.Close()
            Catch e As Exception

                Console.WriteLine("Exception: {0}", e.ToString())
            End Try
            Exit Sub
        End Sub
    End Class
End Namespace

You wouldn't believe the WriteXml method does for you. If you see the output stdData.xml file, it generates a standard XML file that looks like listing 6-27.

Listing 6-27WriteXml method output

<?xml version="1.0" ?>
<DS xmlns="StdNamespace">
  <Student>
    <Name>Mahesh Chand</Name>
    <Address>Meadowlake Dr, Dtown</Address>
  </Student>
  <Student>
    <Name>Mike Gold</Name>
    <Address>NewYork</Address>
  </Student>
  <Student>
    <Name>Mike Gold</Name>
    <Address>New York</Address>
  </Student>
</DS>

The Write xml schema method

This method writes DataSet structure to an XML schema. WriteXmlSchema has four overloaded methods. You can write the data to a stream, text, TextWriter, or Xmlwriter. Listing 6-28 uses XmlWriter for the output.

Listing 6-28. write xml schema sample

Imports System
Imports System.IO
Imports System.Xml
Imports System.Data

Namespace XmlAndDataSetsampB2
    Class XmlAndDataSetSampCls
        Public Shared Sub Main()
            Dim ds As New DataSet("DS")
            ds.[Namespace] = "StdNamespace"
            Dim stdTable As New DataTable("Students")
            Dim col1 As New DataColumn("Name")
            Dim col2 As New DataColumn("Address")
            stdTable.Columns.Add(col1)
            stdTable.Columns.Add(col2)
            ds.Tables.Add(stdTable)

            ' Add student Data to the table
            Dim newRow As DataRow
            newRow = stdTable.NewRow()
            newRow("Name") = "Mahesh chand"
            newRow("Address") = "Meadowlake Dr, Dtown"
            stdTable.Rows.Add(newRow)
            newRow = stdTable.NewRow()
            newRow("Name") = "Mike Gold"
            newRow("Address") = "NewYork"
            stdTable.Rows.Add(newRow)
            ds.AcceptChanges()
            Dim writer As New XmlTextWriter(Console.Out)
            ds.WriteXmlSchema(writer)
            Console.ReadLine()
            Console.ReadLine()
            Exit Sub
        End Sub
    End Class
End Namespace

Output of above listing



XmlData Document and XML

As discussed earlier in this article, the XmlDocument class provides DOM tree structure of XML documents. The XmlDataDocument class comes from XmlDocument, which is comes from XmlNode.

Figure 6-10 shows the XmlDataDocument hierarchy.



Figure 6-10. Xml Data Document hierarchy

Besides overriding the methods of XmlNode and XmlDocument, XmlDataDocument also implements its own methods. The XmlDataDocument class lets you lead relational data using the DataSet object as well as XML documents using the Load and LoadXml methods. As figure 6-11 indicates, you can use a DataSet to load relational data to an XmlDataDocument object and use the Load or LoadXml methods to read an XML document. Figure 6-11 shows a relationship between a Reader, Writer, DataSet, and XmlDataDocument.



Figure 6-11. Reading and writing data using xml Data Document

The XmlDataDocument class extends the functionality of XmlDocument and synchronizes it with DataSet. As you know a DataSet is a powerful object in ADO.NET. As figure 6-11 shows, you can take data from two different sources. First, you can load data from an XML document with the help of XmlReader, and second, you can load data from relational data sources with the help of database provides and DataSet. The neat thing is the data synchronization between these two objects. That means if you update data in a DataSet object, you see results in the XmlDataDocument object and vice versa. For example, if you add a record to a DataSet object, the action will add one node to the XmlDataDocument object representing the newly added record.

Once the data is loaded, you're allowed to use any operations that you were able to use on XmlDocument objects. You can also use XmlReader and XmlWriter objects to read and write the data. 

The xmlData Documet class has property called DataSet. It returns the attached DataSet object with XmlDataDocument. The DataSet property provides you a relational representation of an XML document. Once you've a DataSet object, you can do anything with it such as attaching to a DataGrid. 

You Can use all XML read and write methods of the DataSet object through the DataSet property such as ReadXml, ReadXmlSchema, WriteXml, and WriteXml schema. Refer to the DataSet read write methods in the previous section to see how these methods are used.

Loading Data using Load and LoadXml from the XmlDataDocument

You can use either the Load method or the LoadXml method to load an XML document. The Load method takes a parameter of a filename string, a TextReader, or an XmlReader. Similarly, you can use the LoadXml method. This method passes an XML file name to load the XML file for example:

        Dim doc As New XmlDataDocument()
        doc.Load("c:\Books.xml")

Or you can load an XML fragment, as in the following example:

        Dim doc As New XmlDataDocument()
        doc.LoadsXml("<Record> write something </Record>")

Loading Data Using a DataSet

A DataSet object has methods to read XML documents. These methods are ReadXmlSchema and LoadXml. You use the Load or LoadXml methods to load an XML document the same way you did directly from the XMLDataDocument. Again the Load method takes a parameter of a filename string, TextReader, or XmlReader. Similarly, use the LoadXml method to pass an XML filename through the dataset. For example:

        Dim doc As New XmlDataDocument()
        doc.DataSet.ReadXmlSchema("test. Xsd")

Or 

        doc.DataSet.ReadXml("<Record> write something </Record>")]

Displaying XML Data In a data Set Format

As mentioned previously, you can get DataSet object from an XmlDataDocument object by using its DataSet property. OK, now it's time to see how to do that. The next sample will show you how easy is to display an XML document data in a DataSet format.

To read XML document in a dataset, first you read to document. You can read a document using the ReadXml method of the DataSet object. The DataSet property of XmlDataDocument represents the dataset of XmlDataDocument. After reading a document in a dataset, you can create data views from the dataset, or you can also use a DataSet'sDefaultViewManager property to bind to data-bound controls, as you can see in the following code:

        Dim xmlDatadoc As New XmlDataDocument()
        xmlDatadoc.DataSet.ReadXml("c:\ xmlDataDoc.xml")
        dataGrid1.DataSource = xmlDatadoc.DataSet.DefaultViewManager

Listing 6-29 shows the complete code. As you can see from Listing 6-29, I created a new dataset, Books, fill from the books.xml and bind to a DataGrid control using its DataSource property. To make Listing 6-29 work, you need to create a Windows application and drag a DataGrid control to the form. After doing that, you need to write the Listing 6-29 code on the Form1 constructor or Form load event.

Listing 6-29. XmlDataDocumentSample.cs

    Public Sub New()
        ' Initialize Component and other code here
        ' Create an XmlDataDocument object and read an XML
        Dim xmlDatadoc As New XmlDataDocument()
        xmlDatadoc.DataSet.ReadXml("C:\books.xml")
        ' Create a DataSet object and fill with the dataset
        ' of XmlDataDocument
        Dim ds As New DataSet("Books DataSet")
        ds = xmlDatadoc.DataSet
        ' Attach dataset view to the Data Grid control
        dataGrid1.DataSource = ds.DefaultViewManager
    End Sub

The output of this program looks like figure 6-12. Only a few lines code, and you're all set. Neat huh?



Figure 6-12. XmlDataDocumentSample.cs output

Saving Data from a DataSet to XML 

You can save a DataSet data as an XML document using the Save method of XmlDataDocument. Actually, XmlDataDocument comes from XmlDocument., and the XmlDocument class defines the Save method. I've already discussed that you can use Save method to save your data in a string, stream, TextWriter, and XmlWriter. 

First, you create a DataSet object and fill it using a DataAdapter. The following example reads the Customers table from the Northwind Access database and fills data from the read to the DataSet:

        Dim SQLStmt As String = "SELECT * FROM Customers"
        Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C: \ Northwind.mdb"

        ' Create data adapter
        Dim da As New OleDbDataAdapter(SQLStmt, ConnectionString)

        ' create a new dataset object and fill using data adapter's fill method
        Dim ds As New DataSet()
        da.Fill(ds)

Now, you create an instance of XmlDataDocument with the DataSet as an argument and call the Save method to save the data as an XML document:

        Dim doc As New XmlDataDocument(ds)
        doc.Save("C:\XmlDataDoc.xml")

Listing 6-30 shows a complete program listing. You create an XmlDataDocument object with dataset and call the save method to save the dataset data in an XML file.

Listing 6-30. Saving the dataset data to an XML document

Imports System.Data
Imports System.Data.OleDb
Imports System.Xml

Namespace DataDocsampB2
    Class Class1
        Private Shared Sub Main(ByVal args As String())
            ' create SQL Query
            Dim SQLStmt As String = "SELECT * FROM Customers"
            ' Connection string
            Dim ConnectionString As String = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source = C:\ Northwind.mdb"
            ' Create data adapter
            Dim da As New OleDbDataAdapter(SQLStmt, ConnectionString)
            ' create a new dataset object and fill using data adapter's fill method
            Dim doc As New DataSet()
            ' Now use SxlDataDocument's Save method to save data as an XML file XmlDataDocument doc = new XmlDataDocument(ds);
            doc.Save("C:\XmlDataDoc.xml")
        End Sub
    End Class
End Namespace

XmlDataDocument: Under the Hood

After Looking at Listing 6-29, which illustrated the reading an XML document in a DataGrid control, you must be wondering how it happened? It's all the magic of the DataSet object. The DataSet object handles everthing for under the hood:

doc.DataSet.ReadXml("C:\outdata.xml")

As you see in this first line calling DataSet.ReadXml method to read an XML document. The DataSet extracts the document and defines tables and columns for you.

Generally, the root node of the XML document becomes a table; the document's Name, Namespace, NamespaceURI, and prefix of the XML document become the dataset's Name, Namespace, NamespaceURI, and Prefix respectively. If an element's children have one or more children, they become another table inside the main table in a nested format. Anything left from the tables becomes columns of the table. The value of node is added as a row in a table. DataSet takes care of all of this under the hood.

Conclusion

Hope this article would have helped you in understanding ADO .NET and XML. See other articles on the website also for further reference.