Essential ADO.NET: XML and Data Access Integration

This chapter is from the book

Essential ADO.NET

Learn More Buy

This chapter is from the book

This chapter is from the book 

Essential ADO.NET

Learn More Buy

7.5 The XmlDataDocument Class

Having come at the problem of data representation from the point of view of the data storage mechanism—that is, the relational database—we've thus far represented the in-memory object model as though it, too, were a relational database. Chapter 2 touches on the XML Infoset as a different abstraction for data, and you looked at the XML DOM, one of its in-memory data representations. The classes that are used in the relational model parallel those in a relational database; you have a DataSet consisting of DataTables, DataColumns, DataRows, and DataRelations. You even have a mechanism—DataView—to filter and sort Data-Tables in memory. The filtering mechanism uses a language similar to SQL.

In the DOM model, XmlDocuments consist of XmlNodes. The XmlDocument can use the XML query language, XPath, to produce either single nodes or nodesets. You can also transform an entire XmlDocument using the XSLT transformation language, producing XML, HTML, or any other format of text output. The .NET class that encapsulates this function is XslTransform. You can traverse the XmlDocument structure either sequentially or using a navigation paradigm. Navigation is represented by a series of classes that implement the XPathNavigator interface. XPathNavigator is optimized for XPath queries; its queries can return XPathNodeIterator or scalar values.

Sometimes it would be useful to integrate these two models—for example, to update a portion of a DOM document based on data in a relational database, or to query a DataSet using XPath as though it were a DOM. The class that lets you treat data as though it were both a DOM and a DataSet, exposing updatability but maintaining consistency in each model, is XmlDataDocument.

The XmlDataDocument class works around the limitation of the strict relational model by enabling partial mapping on DataSet. The DataSet class (and its underlying XML format) works only with homogeneous rowsets or hierarchies, in which all rows contain the same number of columns in the same order. When you attempt to map a document in which columns are missing in rows of the same type, as in Listing 7–26, the XmlRead function compensates by mapping every combination of columns, and setting the ones that do not exist in any level of hierarchy to DBNull.Value.

Listing 7–26 Missing columns in rows

<root>
  <document>
     <name>Bob</name>
     <address>111 Any St</address>
  </document>
  <document>
     <name>Bird</name>
     <livesin>tree</livesin>
  </document>
</root>

The XML Infoset has no limitation to homogeneous data. When data is semistructured or contains mixed content (elements and text nodes mixed), as in Listing 7–27, coercing the data into a relational model will not work. An error, "The same table (noun) cannot be the child table in two nested relations.," is produced in this case. You can still integrate, at least partially, XML data that is shaped differently; you use the DataSet through the XmlDataDocument class. In addition, you can preserve white space and maintain element order in the XmlDocument, but when such a document is mapped to a DataSet, these extra representation semantics may be lost. XML comments and processing instructions will also be lost in the DataSet representation.

Listing 7–27 A document containing mixed content

<book>
<chapter>
<title>Testing your <noun>typewriter</noun></title>
<p>The quick brown <noun>fox</noun> jumps over
the lazy <noun>dog</noun></p>
</chapter>
</book>

7.5.1 XmlDataDocuments and DataSets

As shown in Figure 7–3, an XmlDataDocument is an XmlDocument. That's because

Figure 7-3 XmlDataDocument and related classes.

Data can be loaded into an XmlDataDocument through either the DataSet interfaces or the XmlDocument interfaces. You can import the relational part of the XML document into DataSet by using an explicit or implied mapping schema, as shown in Listing 7–28. Whether changes are made through DataSet or through XmlDataDocument, the changed values are reflected in both objects. The full-fidelity XML is always available through the XmlDataDocument.

Listing 7–28 Loading a DataSet through the XmlDataDocument

XmlDataDocument datadoc = new XmlDataDocument();
datadoc.DataSet.ReadXmlSchema("c:\\authors.xsd");
datadoc.Load ("c:\\authors.xml");

DataSet ds = datadoc.DataSet;

// use DataSet as usual
foreach (DataTable t in ds.Tables)
  Console.WriteLine(
    "Table " + t.TableName + " is in dataset");

In addition to the DOM-style navigation supported by XmlDocument, the XmlDataDocument adds methods to let you get an Element from a DataRow or a DataRow from an Element. Listing 7–29 shows an example.

Listing 7–29 Using GetElementFromRow

XmlDataDocument datadoc = new XmlDataDocument();

datadoc.DataSet.ReadXmlSchema(
  "c:\\xml_schemas\\cust_orders.xsd");

datadoc.Load(new XmlTextReader(
  "http://localhost/northwind/template/modeauto1.xml"));

XmlElement e = datadoc.GetElementFromRow(
    datadoc.DataSet.Tables[0].Rows[2]);
Console.WriteLine(e.InnerXml);

You can create an XmlDataDocument using a prepopulated DataSet, as shown in Listing 7–30. Any data in the DataSet is used to construct a DOM representation. This DOM is exactly the same as the XML document that would be serialized using DataSet.WriteXml. The only difference, a trivial one, is that DataSet.WriteXml writes an XML directive at the beginning and XmlDocument.Save does not.

Listing 7–30 Creating an XmlDataDocument from a DataSet

DataSet ds = new DataSet();

// load the DataSet
SqlDataAdapter da = new SqlDataAdapter(
        "select * from authors;select * from titleauthor",
        "server=localhost;database=pubs;uid=sa");
da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
da.Fill(ds);

// tweak the DataSet schema
ds.Tables[0].TableName = "authors";
ds.Tables[1].TableName = "titleauthor";
ds.Relations.Add(
   ds.Tables[0].Columns["au_id"], 
   ds.Tables[1].Columns["au_id"]);
ds.Relations[0].Nested = true;

XmlDataDocument dd = new XmlDataDocument(ds);

// write the document
dd.Save("c:\\temp\\xmldoc.xml");
// write the dataset
dd.DataSet.WriteXml("c:\\temp\\dataset.xml");

An XmlDataDocument can also be populated by an XML document through its Load method, as shown in Listing 7–31. What makes XmlDataDocument unique is that you can load an entire XML document by using XmlDataDocument.Load, but the DataSet member contains only the tables that existed in the DataSet's schema at the time that you called Load. Removing the ReadXmlSchema line in Listing 7–31 results in a complete document in the DOM, but a DataSet that, when serialized, contains only an empty root element. Reading a schema that contains only authors will result in a complete DOM and a DataSet containing only authors. Attempting to use an "embedded schema plus document"–style XML document produced by using DataSet.WriteXml with XmlWriteMode.WriteSchema, or using an XSD inline schema recognized with the XmlValidatingReader, works to load the document, but this schema is not used to populate the DataSet; the DataSet contains no data.

Listing 7–31 Loading an XmlDataDocument from a document

XmlDataDocument dd = new XmlDataDocument();

dd.DataSet.ReadXmlSchema(
   "c:\\xml_schemas\\au_title.xsd");
dd.Load("c:\\xml_documents\\au_title.xml");

// write the document
dd.Save("c:\\temp\\xmldoc.xml");
// write the dataset
dd.DataSet.WriteXml("c:\\temp\\dataset.xml");

Here are a few rules to keep in mind when you're using XmlDataDocument:

• The mapping of Document to DataSet (using XmlDataDocument.DataSet.ReadXmlSchema or other means) must already be in place when you load the XML document using XmlDataDocument.Load.

• Each named piece of data represented in the XML schema can be a child of only one element. In general, this means that an XML schema cannot use global xsd:element elements.

• Tables cannot be added to the schema mapping after a document is loaded.

• Documents cannot be loaded after data has been loaded, either through XmlDataDocument.Load or by a DataAdapter.

You can use XmlDataDocument to coerce mixed content and other semi-structured data into a somewhat relational form. This technique could help you use the nonrelational document that you looked at in the beginning of this section. The relational schema must be defined so that each simple element type appears unambiguously in any table. Using the "Testing Your Typewriter" document in Listing 7–27 as an example, you cannot map the document so that noun elements appear under both title elements and p elements, as you saw earlier. Neither can you use a schema that maps noun under only title or only p elements. When you try to use a DataSet schema with noun only as a child of p (hoping to map only nouns that appear in paragraphs), you receive the error "SetParentRow requires a child row whose table is "p", but the specified row's Table is title." The way to map all noun elements is to use a schema in which noun is not a child of any other element. This produces a single noun table containing noun elements from (title)s and (p)aragraphs.

Another possible use for XmlDataDocument is to merge new data from a relational database into an existing XML document. This works, but with the limitations noted earlier. Consider a DataSet schema containing authors and rows from a nonrectangular document. You would first try to load both documents into the XmlDataDocument, but a second document cannot be loaded when the XmlDataDocument already contains data. The code in Listing 7–32 fails when trying to load the second document.

Listing 7–32 Merging data with XmlDataDocument; this fails

try 
{
  XmlDataDocument dd = new XmlDataDocument();

  // schema contains authors and document
  dd.DataSet.ReadXmlSchema(@"c:\xml_schemas\au_nonr.xsd");

  // this document contains document
  dd.Load(@"c:\xml_documents\nonrect.xml");

  // this document contains authors
  // this fails
  dd.Load(@"c:\xml_documents\authors_doc.xml");
	
  foreach (DataTable t in dd.DataSet.Tables)
    Console.WriteLine("table {0} contains {1} rows", 
      t.TableName, t.Rows.Count);
}
catch (Exception e)
{
  Console.WriteLine(e.Message);
}

Attempting to populate the DataSet with a DataAdapter after a document has been loaded produces partial success. The code in Listing 7–33 produces a DataSet containing two tables, but a document containing only the data originally loaded by calling XmlDataDocument.Load.

Listing 7–33 Merging documents with XmlDataDocument; this doesn't synchronize

try 
{
  XmlDataDocument dd = new XmlDataDocument();

  // schema contains authors and document
  dd.DataSet.ReadXmlSchema(@"c:\xml_schemas\au_nonr.xsd");

  // this document contains document
  dd.Load(@"c:\xml_documents\nonrect.xml");

  // add authors
  SqlDataAdapter da = new SqlDataAdapter(
    "select * from authors",
    "server=localhost;uid=sa;database=pubs");

  da.Fill(dd.DataSet, "authors");
	
  // both appear in the DataSet
  foreach (DataTable t in dd.DataSet.Tables)
    Console.WriteLine("Table {0} contains {1} rows", 
      t.TableName, t.Rows.Count);

  // no authors in the document
  dd.Save(@"c:\temp\au_nonr.xml");
}
catch (Exception e)
{
  Console.WriteLine(e.Message);
}

The correct way to accomplish a merge of two documents is to use two DataSets and the DataSet.Merge method. The second DataSet can be either standalone or part of an XmlDataDocument, as shown in Listing 7–34. If data is merged into a DataDocument's DataSet, however, the schema for both tables must be loaded when the original document is loaded or else an error message will result during DataSet.Merge.

Listing 7–34 Merging documents with XmlDataDocument; this works

  XmlDataDocument dd = new XmlDataDocument();

  // schema contains authors and document
  dd.DataSet.ReadXmlSchema(@"c:\xml_schemas\au_nonr.xsd");

  // this document contains document
  dd.Load(@"c:\xml_documents\nonrect.xml");

  // 1. either of these will work

  // add authors
  SqlDataAdapter da = new SqlDataAdapter(
    "select * from authors",
    "server=localhost;uid=sa;database=pubs");

  DataSet ds = new DataSet();
  da.Fill(ds, "authors");
  dd.DataSet.Merge(ds);

  // 2. either of these will work
  XmlDataDocument dd2 = new XmlDataDocument();
  dd2.DataSet.ReadXmlSchema(@"c:\xml_schemas\au_nonr.xsd");

  // add authors
  dd2.Load(@"c:\xml_documents\authors_doc.xml");
  dd.DataSet.Merge(dd2.DataSet);

  // both appear in the DataSet
  foreach (DataTable t in dd.DataSet.Tables)
    Console.WriteLine("Table {0} contains {1} rows", 
      t.TableName, t.Rows.Count);

  // both in the document
  dd.Save(@"c:\temp\au_nonr.xml");
}
catch (Exception e)
{
  Console.WriteLine(e.Message);
}

7.5.2 XmlDataDocument and DataDocumentXPathNavigator

An additional advantage of using XmlDataDocument is that you can query the resulting object model using either the SQL-like syntax of DataView filters or the XPath query language. DataView filters produce sets of rows and have simple support for parent-child relationships via the CHILD keyword. XPath is a more full-featured query language that can produce sets of nodes or scalar values. You can use XPath directly via the SelectSingleNode and SelectNode methods that XmlDataDocument inherits from XmlDocument. The XPathNavigator class also lets you use precompiled XPath queries. Resultsets from XPath queries are exposed as XPathNodeIterators. You can use XPathNavigators in input to the XSLT transformation process exposed through the XslTransform class. You can also use XPathNavigators to update nodes in their source document, using the presence of an IHasXMLNode interface on a result node and using IHasXMLNode.GetNode to get the underlying (updatable) XmlNode.

DataDocumentXPathNavigator is a private subclass of XPathNavigator that provides cursor-based navigation of the "XML view" of the data in an XmlDataDocument. As with the XPathNavigator returned by XmlDocument.CreateNavigator, multiple navigators can maintain multiple currency positions; in addition, a DataDocumentXPathNavigator's position in the XmlDocument is synchronized with its position in the DataSet. Programs that depend on positional navigation under classic ADO's client cursor engine or DataShape provider can be migrated to this model. Listing 7–35 shows an example of using XPathNavigator with XmlDataDocument.

Listing 7–35 Using XPathNavigator

XmlDataDocument datadoc = new XmlDataDocument();

datadoc.Load(new XmlTextReader(
  "http://localhost/nwind/template/modeauto1.xml"));

XPathNavigator nav = datadoc.CreateNavigator();
XPathNodeIterator i = nav.Select("//customer");
Console.WriteLine(
  "there are {0} customers", i.Count);

A final example, Listing 7–36, combines all the XmlDataDocument features shown so far. A DataSet is created from multiple results obtained from a SQL Server database. An XmlDataDocument and an XPathNavigator are created over the DataSet. Using the XPathNavigator, an XPath query returns a set of nodes in the parent (contract columns in the authors table) based on criteria in the children. The resulting XPathNodeIterator is used to update the XmlDocument nodes. Because the XmlDocument stays synchronized with the DataSet, the rows are then updated using a DataAdapter.

Listing 7–36 Updating through an XPathNavigator

DataSet ds = new DataSet("au_info");

// load the DataSet
SqlDataAdapter da = new SqlDataAdapter(
  "select * from authors;select * from titleauthor",
  "server=localhost;database=pubs;uid=sa");
da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
da.Fill(ds);

// tweak the DataSet schema
ds.Tables[0].TableName = "authors";
ds.Tables[1].TableName = "titleauthor";
ds.Relations.Add(
  ds.Tables[0].Columns["au_id"], 
  ds.Tables[1].Columns["au_id"]);
ds.Relations[0].Nested = true;

XmlDataDocument dd = new XmlDataDocument(ds);
// This must be set to false
// to edit through the XmlDocument nodes
dd.DataSet.EnforceConstraints = false;
XPathNavigator nav = dd.CreateNavigator();

// get the "contract" column (node)
// for all authors with a royalty percentage < 30%
XPathNodeIterator i = nav.Select(
 "/au_info/authors/contract[../titleauthor/royaltyper<30]");
while (i.MoveNext() == true)
{
  XmlNode node = ((IHasXmlNode)i.Current).GetNode();
  node.InnerText = "false";
}

SqlCommandBuilder bld = new SqlCommandBuilder(da);
da.Update(dd.DataSet, "authors");