The XmlDataDocument Object

The XmlDataDocument Object

In Chapter 9 we discussed the XmlDocument object and how it is used to access the hierarchical data of the nodes of an XML document loaded into memory. We have also discussed throughout this book how to retrieve and access relational data from a (traditional) SQL database. On the one hand, what if your data comes from an XML source, but you want (or only know how) to navigate and manipulate it by using rows and relational techniques? On the other hand, what if your data comes from an SQL database and you want to navigate and manipulate it by using nodes and XML techniques?

The answer to these questions is the XmlDataDocument class. It is derived from the XmlDocument class but adds an important dimension. While maintaining a single internal copy of data, it provides both XML node access, as the XmlDocument class, and relational access via a DataSet property. The XmlDataDocument automatically synchronizes the two views (or access methods), so that any change via one technique or technology is immediately accessible via the other. This approach allows the mixing and matching of data sources and access techniques.

Continuing with the ADO-XML project, let's look at two scenarios.

1. Add two additional buttons immediately below the btnExecuteXML button from the Windows Forms tab of the Toolbox.

2. In the Properties window, set the Name property of the first button to btnNavigateSQL and set the Text property to Navigate SQL.

3. In the Properties window, set the Name property of the second button to btnAddRows and set the Text property to "Add rows to XML".

4. Add the XPath namespace to the end of the list of Import statements at the top of the file:

    Imports System.Xml.XPath  

5. Add the following two subroutines to the frmXML class:

    Private Sub btnNavigateSQL_Click(ByVal sender As System.Object, _         ByVal e As System.EventArgs) Handles _        btnNavigateSQL.Click   Dim cn As New SqlConnection _        ("data source=localhost;initial cata log=pubs;user id=sa")   Dim da As New SqlDataAdapter("Select * from authors", cn)   Dim ds As New DataSet()   ' Fill DataSet with data from relational database.   da.Fill(ds, "authors")   ' Create a XmlDataDocument based on the existing DataSet   Dim xmlDoc As New Xml.XmlDataDocument(ds)   ' Get a Navigator for the XmlDataDocument   Dim xmlNav As XPathNavigator = xmlDoc.CreateNavigator()   ' Get all the Author last names from California (state = CA)   Dim xIterator As XPathNodeIterator   xIterator = _       xmlNav.Select("//authors[state='CA']/au_lname')   ' Iterate over all of the selected nodes and   ' display the author last Names.   Dim str As New System.Text.StringBuilder()   While (xIterator.MoveNext())     str.Append(xIterator.Current.Value & ControlChars.CrLf)   End While   MsgBox(str.ToString) End Sub Private Sub btnAddRows_Click(ByVal sender As System.Object, _     ByVal e As System.EventArgs) Handles btnAddRows.Click   Dim dsPubs As New DataSet()   ' Read in XML from file.   dsPubs.ReadXml("..\Pubs.xml")   ' NOW add a new row.   Dim row As DataRow = dsPubs.Tables("Publishers").NewRow()   row("pub_name") = "Newbie Publishing Corp."   row("city") = "New York"   row("state") = "NY"   row("Country") = "USA"   dsPubs.Tables("Publishers").Rows.Add(row)   ' Bind DataSet to Data Grid to see new data.   grdData.DataMember = "publishers"   grdData.DataSource = dsPubs End Sub 

The subroutine btnNavigateSQL_Click reads in data from a SQL Server database and then navigates a subset of the records via an XPATH query to iterate over the selected data. The key lines in this routine are

 Dim xmlDoc As New Xml.XmlDataDocument(ds)  ' Get a Navigator for the XmlDataDocument. Dim xmlNav As XPathNavigator = xmlDoc.CreateNavigator() ' Get all the Author last names from California (state = CA). Dim xIterator As XPathNodeIterator xIterator = _      xmlNav.Select("//authors[state='CA']/au_lname') 

First, the filled DataSet is associated with a new XmlDataDocument. An XPathNavigator is created on this XmlDataDocument, allowing us to create an XPathNodeIterator. An XPATH query string is passed to the Select method, where the query is defined to return the author last name (au_lname) field, for all the author nodes where the state is "CA". The routine then iterates across all the nodes selected by the query, building a string that contains the author last names. This string is then displayed in a message box, as shown in Figure 10.3.

Figure 10.3. Message box with XML data retrieved directly from a DataSet

The second subroutine, btnAddRows_Clicks, goes the other way. It first executes ReadXml to read XML data from the file publishers.xml into the dsPubs DataSet, as in Listing 10.1. This method automatically creates a publishers table in the DataSet. The subroutine then adds new data via relational techniques and objects such as the DataRow. A new DataRow is created with the schema of the publishers table, and the row columns are assigned values. The new row is added to the publishers table and the result is displayed in a DataGrid, as shown in Figure 10.4.

Figure 10.4. DataGrid with data from XML file and added row