(untagged)

Programmatically using ADO.NET and XML

Xinjie ZHANG

0.00/5 (No votes)

15 Sep 2004

With ADO.NET, a database can be regarded as a virtual XML document. XPath query and XSLT transformation are naturally available.

Download demo project - 489 Kb

Introduction

ADO.NET can be used as a powerful XML middleware. Although coming from ADO, it has been entirely redesigned for a better support for Web applications. A main feature of ADO.NET is its disconnected recordsets, known as DataSet. It acts as a feature-rich in-memory database or data cache. In other words, it does not maintain any database server cursors. All recordset�s database characteristics are available in DataSet, such as sorting, paging, filtered views, relations, indexing, and primary/foreign keys.

Native support for XML is another principal feature for ADO.NET. In native mode, record tables are stored as XML documents where schema and data are treated as distinct and replaceable elements. It is quite different from ADO. The ADO native storage format for a recordset is the Advanced Data Table Gram (ADTG) file format, which is a proprietary, binary schema that represents a recordset�s in-memory image. The DataSet objects in ADO.NET can load its data from variant sources: a database server, a local or remote XML file and any accessible system resources. Once the data is loaded, they can be either treated as a record table or a virtual XML document. We principally discuss the latter. Now, I will give you several use cases. Each use case is a standalone unit test which can be run under NUnit. So you should install a latest version of NUnit. More information about its installation and usage can be found here.

Before executing these examples, you should also setup an ODBC connection for the database with the following steps:

Extract the download package. There is an access file �NWIND_2002.MDB� in the folder �UnitTest_ADO.NET_XML�.
Open Control Panel -> Administrative Tools -> Data Source.
Choose the System Data Sources tab.
Click �Add� button and choose �Microsoft Access Driver (*.mdb)� from the list. Then an ODBC configuration dialog appears.
Enter �XmlDb_NorthWind� as the Data Source Name (DSN), and choose the Access file �NWIND_2002.MDB� as the data source.

Here are the relationships between the tables in the Access file:

Figure 1: Relationships between tables

Transform a single table to XML document

With ADO.NET, we can easily build a virtual XML document on top of recordset tables. Its root element has exactly the same name as the DataSet object. Tables in the DataSet object present as child nodes of the root element. For example, there is a DataSet named �XmlDb�. When it is transformed into XML document, the name of its root element will be �XmlDb� (refer to Figure 2). All rows in the table �Customers� will be mapped to a unique child node of the root element, whose node name matches the table name �Customers�. In Figure 2, �CustomID�, �CompanyName�, etc. are fields of the table �Customers�. When they are transformed into XML, they present as child nodes of �Customers�. Their node name will be the same as the field name and node value will be the value of that field.

Figure 2: Transformation of a single table to XML document.

The following simple example explains how ADO.NET maps the DataSet object to a virtual XML document:

using System;
using System.IO;
using System.Xml;
using System.Data;
using System.Data.Odbc;
using NUnit.Framework;
[Test] public void SingleTable2XML() 
{
//Create an ODBC connection to the database. Here it is an Access file

OdbcConnection conn = new OdbcConnection("DSN=XmlDb_NorthWind");

//Create a DataSet with a name "XmlDb"

DataSet dataset = new DataSet("XmlDb");

//Create a DataAdapter to load data from original data source to the DataSet

OdbcDataAdapter adapter = new OdbcDataAdapter();
adapter.SelectCommand = new OdbcCommand("SELECT * FROM Customers", conn);
adapter.Fill(dataset, "Customers");

//Create a virtual XML document on top of the DataSet

XmlDataDocument doc = new XmlDataDocument(dataset); 

//Output this XML document

doc.Save(Console.Out);

//NUnit test to confirm the result is exactly what we expect

Assert.AreEqual("XmlDb", doc.DocumentElement.LocalName);
Assert.AreEqual("Customers", doc.DocumentElement.FirstChild.LocalName);
}

Output:

<?xml version="1.0" encoding="Windows-1252"?>
<XmlDb>
  <Customers>
    <CustomerID>ALFKI</CustomerID>
    <CompanyName>Alfreds Futterkiste</CompanyName>
    <ContactName>Maria Anders</ContactName>
    <ContactTitle>Sales Representative</ContactTitle>
    <Address>Obere Str. 57</Address>
    <City>Berlin</City>
    <PostalCode>12209</PostalCode>
    <Country>Germany</Country>
    <Phone>030-0074321</Phone>
    <Fax>030-0076545</Fax>
  </Customers>
   ��
</XmlDb>

Transform Master-Detail tables to XML document

Entity-Relationship model is a long-tested approach to map the real world to database structure. Relationships are often represented as Master-Detail tables, which can be naturally transformed into XML parent/child nodes, with ADO.NET. The Figure 3 shows you such a mapping:

Figure 3: Transformation of Master-Detail tables to XML document.

The key point to generate nested XML nodes is to setup a DataRelation object to link the master table and the detail table with the primary key and the foreign key. Like this:

DataColumn primarykey = dataset.Tables["Customers"].Columns["CustomerID"];
DataColumn foreignkey = dataset.Tables["Orders"].Columns["CustomerID"];
DataRelation relation = dataset.Relations.Add(primarykey, foreignkey);

It is not enough. You must also set the �Nested� property of the DataRelation to �true�:

relation.Nested = true;

Full code:

using System;
using System.IO;
using System.Xml;
using System.Data;
using System.Data.Odbc;
using NUnit.Framework;
[Test] public void MasterDetailTables2XML() 
{
//Create an ODBC connection to the database. Here it is an Access file

OdbcConnection conn = new OdbcConnection("DSN=XmlDb_NorthWind");

//Create a DataSet with a name "XmlDb"

DataSet dataset = new DataSet("XmlDb");

//Load master table from original data source to the DataSet

OdbcDataAdapter adapter = new OdbcDataAdapter();
adapter.SelectCommand = new OdbcCommand("SELECT * FROM Customers", conn);
adapter.Fill(dataset, "Customers");

//Load detail table from original data source to the DataSet

adapter.SelectCommand = new OdbcCommand("SELECT * FROM Orders", conn);
adapter.Fill(dataset, "Orders");

//Get the primary key column from the master table

DataColumn primarykey = dataset.Tables["Customers"].Columns["CustomerID"];

//Get the foreign key column from the detail table

DataColumn foreignkey = dataset.Tables["Orders"].Columns["CustomerID"];

//Assign a relation

DataRelation relation = dataset.Relations.Add(primarykey, foreignkey);

//Ask ADO.NET to generate nested XML nodes

relation.Nested = true;

//Create a virtual XML document on top of the DataSet

XmlDataDocument doc = new XmlDataDocument(dataset); 

//Output this XML document

doc.Save(Console.Out);

//NUnit test to confirm the result is exactly what we expect

Assert.AreEqual("XmlDb", doc.DocumentElement.LocalName);
Assert.AreEqual("Customers", doc.DocumentElement.FirstChild.LocalName);
Assert.AreEqual("Customers", 
           doc.GetElementsByTagName("Orders")[0].ParentNode.LocalName);
}

Output:

<XmlDb>
  <Customers>
    <CustomerID>ALFKI</CustomerID>
    <CompanyName>Alfreds Futterkiste</CompanyName>
    <ContactName>Maria Anders</ContactName>
    <ContactTitle>Sales Representative</ContactTitle>
    <Address>Obere Str. 57</Address>
    <City>Berlin</City>
    <PostalCode>12209</PostalCode>
    <Country>Germany</Country>
    <Phone>030-0074321</Phone>
    <Fax>030-0076545</Fax>
    <Orders>
      <OrderID>10643</OrderID>
      <CustomerID>ALFKI</CustomerID>
      <OrderDate>1995-09-25T00:00:00.0000000+02:00</OrderDate>
      <RequiredDate>1995-10-23T00:00:00.0000000+02:00</RequiredDate>
      <ShippedDate>1995-10-03T00:00:00.0000000+02:00</ShippedDate>
      <Freight>29.4600</Freight>
      <ShipName>Alfreds Futterkiste</ShipName>
      <ShipAddress>Obere Str. 57</ShipAddress>
      <ShipCity>Berlin</ShipCity>
      <ShipPostalCode>12209</ShipPostalCode>
      <ShipCountry>Germany</ShipCountry>
    </Orders>
    <Orders>
      <OrderID>10692</OrderID>
      <CustomerID>ALFKI</CustomerID>
      <OrderDate>1995-11-03T00:00:00.0000000+01:00</OrderDate>
      <RequiredDate>1995-12-01T00:00:00.0000000+01:00</RequiredDate>
      <ShippedDate>1995-11-13T00:00:00.0000000+01:00</ShippedDate>
      <Freight>61.0200</Freight>
      <ShipName>Alfred's Futterkiste</ShipName>
      <ShipAddress>Obere Str. 57</ShipAddress>
      <ShipCity>Berlin</ShipCity>
      <ShipPostalCode>12209</ShipPostalCode>
      <ShipCountry>Germany</ShipCountry>
    </Orders>
  </Customers>
   ��
</XmlDb>

Query a database with XPath

.NET Framework implements all DOM interfaces in its System.Xml namespace. Moreover, it has integrated XPath in the XmlNode level as an extension to DOM. So, once a virtual XML document is built, it can be queried with XPath immediately. For example, in last section, we have created an XML document which represents �Customers� and their correspondent �Orders�. Now, we want to find out all customers in Berlin and have asked to ship the ordered products to Germany. We can perform such a search on the XML document with one line of code:

XmlNodeList nodeList = 
    doc.SelectNodes("/XmlDb/Customers/Orders[../City='Berlin'" + 
                                 " and ShipCountry='Germany']");

It will put all qualified nodes into an XmlNodeList which can be visited with a �foreach�:

foreach (XmlNode node in nodeList)
{
......
}

Here is a full example:

using System;
using System.IO;
using System.Xml;
using System.Data;
using System.Data.Odbc;
using NUnit.Framework;
[Test] public void QueryWithXPath() 
{
//Create an ODBC connection to the database. Here it is an Access file

    OdbcConnection conn = new OdbcConnection("DSN=XmlDb_NorthWind");

    //Create a DataSet with a name "XmlDb"

    DataSet dataset = new DataSet("XmlDb");

    //Load master table from original data source to the DataSet

    OdbcDataAdapter adapter = new OdbcDataAdapter();
    adapter.SelectCommand = new OdbcCommand("SELECT * FROM Customers", conn);
    adapter.Fill(dataset, "Customers");

    //Load detail table from original data source to the DataSet

    adapter.SelectCommand = new OdbcCommand("SELECT * FROM Orders", conn);
    adapter.Fill(dataset, "Orders");

    //Get the primary key column from the master table

    DataColumn primarykey = dataset.Tables["Customers"].Columns["CustomerID"];

    //Get the foreign key column from the detail table

    DataColumn foreignkey = dataset.Tables["Orders"].Columns["CustomerID"];

    //Assign a relation

    DataRelation relation = dataset.Relations.Add(primarykey, foreignkey);

    //Ask ADO.NET to generate nested XML nodes

    relation.Nested = true;

    //Create a virtual XML document on top of the DataSet

    XmlDataDocument doc = new XmlDataDocument(dataset); 

    //Create an output buffer

    StringBuilder stringBuilder = new StringBuilder();
    stringBuilder.Append("<RESULTS>");

    //Perform an XPath query

    XmlNodeList nodeList = 
       doc.SelectNodes("/XmlDb/Customers/Orders[../City='Berlin'" + 
                                    " and ShipCountry='Germany']");

    //Visit results in the list

    foreach (XmlNode node in nodeList)
    {
        stringBuilder.Append(node.OuterXml);

        //NUnit tests to confirm the result is exactly what we expect

        Assert.AreEqual("ShipCountry", node.ChildNodes[10].LocalName);
        Assert.AreEqual("Germany", node.ChildNodes[10].InnerText);
        Assert.AreEqual("City", node.ParentNode.ChildNodes[5].LocalName);
        Assert.AreEqual("Berlin", node.ParentNode.ChildNodes[5].InnerText);
    }
    stringBuilder.Append("</RESULTS>");
    XmlDocument docResult = new XmlDocument();
    docResult.LoadXml(stringBuilder.ToString());
    docResult.Save(Console.Out);
}

Output:

<?xml version="1.0" encoding="Windows-1252"?>
<Results>
  <Orders>
    <OrderID>10643</OrderID>
    <CustomerID>ALFKI</CustomerID>
    <OrderDate>1995-09-25T00:00:00.0000000+02:00</OrderDate>
    <RequiredDate>1995-10-23T00:00:00.0000000+02:00</RequiredDate>
    <ShippedDate>1995-10-03T00:00:00.0000000+02:00</ShippedDate>
    <Freight>29.4600</Freight>
    <ShipName>Alfreds Futterkiste</ShipName>
    <ShipAddress>Obere Str. 57</ShipAddress>
    <ShipCity>Berlin</ShipCity>
    <ShipPostalCode>12209</ShipPostalCode>
    <ShipCountry>Germany</ShipCountry>
  </Orders>
  <Orders>
    <OrderID>10692</OrderID>
    <CustomerID>ALFKI</CustomerID>
    <OrderDate>1995-11-03T00:00:00.0000000+01:00</OrderDate>
    <RequiredDate>1995-12-01T00:00:00.0000000+01:00</RequiredDate>
    <ShippedDate>1995-11-13T00:00:00.0000000+01:00</ShippedDate>
    <Freight>61.0200</Freight>
    <ShipName>Alfred's Futterkiste</ShipName>
    <ShipAddress>Obere Str. 57</ShipAddress>
    <ShipCity>Berlin</ShipCity>
    <ShipPostalCode>12209</ShipPostalCode>
    <ShipCountry>Germany</ShipCountry>
  </Orders>
  ......
</Results>

The mapping between XmlElement and DataRow

Although accessing DataSet with XML provides some unique advantages, we still need to obtain some complemental information about the data. For example, ADO.NET marks every DataRow with its current state, namely Added, Deleted, Detached, Modified, and Unchanged. These states are important when we perform update to the data source. Fortunately, XmlDataDocument provides a useful method to help us get correspondent DataRow from the XmlElement. Once we get the DataRow, its current state can be obtained through its property RowState:

DataRow row = xmlDataDocument.GetRowFromElement(xmlElement);
Console.Write("RowState: ");
switch(row.RowState)
{
    case DataRowState.Added:
        Console.WriteLine("Added");break;
    case DataRowState.Deleted:
        Console.WriteLine("Deleted");break;
    case DataRowState.Detached:
        Console.WriteLine("Detached");break;
    case DataRowState.Modified:
        Console.WriteLine("Modified");break;
    default:
        Console.WriteLine("Unchanged");break;
}

We can also detect whether there are errors after performing update:

DataRow row = xmlDataDocument.GetRowFromElement(xmlElement);
if (row.HasErrors)
    Console.WriteLine(row.RowError);
else
    Console.WriteLine("Everything is OK.");

XmlDataDocument provides another method GetElementFromRow to map a DataRow to XmlElement. Here is a complete example:

using System;
using System.IO;
using System.Xml;
using System.Data;
using System.Data.Odbc;
using NUnit.Framework;
[Test] public void MappingBetweenXmlElementAndDataRow() 
{
    //Create an ODBC connection to the database. Here it is an Access file

    OdbcConnection conn = new OdbcConnection("DSN=XmlDb_NorthWind");

    //Create a DataSet with a name "XmlDb"

    DataSet dataset = new DataSet("XmlDb");

    //Create a DataAdapter to load data from 

    //original data source to the DataSet

    OdbcDataAdapter adapter = new OdbcDataAdapter();
    adapter.SelectCommand = new OdbcCommand("SELECT * FROM Products", conn);
    adapter.Fill(dataset, "Products");

    //Create a virtual XML document on top of the DataSet

    XmlDataDocument doc = new XmlDataDocument(dataset); 

    Console.WriteLine("=========== GetRowFromElement ================");

    //Perform XPath query

    XmlNodeList nodeList = doc.SelectNodes("/XmlDb/Products[CategoryID=3]");
    foreach (XmlNode node in nodeList)
    {
        //Map XmlElement to DataRow

        DataRow row = doc.GetRowFromElement((XmlElement) node);
        Console.WriteLine("Product Name = " + row["ProductName"]);
        Assert.AreEqual(3, row["CategoryID"]);
    }

    Console.WriteLine("=========== GetElementFromRow ================");

    //Perform ADO.NET native query

    DataRow[] rows = dataset.Tables["Products"].Select("CategoryID=3");
    foreach (DataRow row in rows)
    {
        //Map DataRow to XmlElement

        XmlElement elem = doc.GetElementFromRow(row);
        Console.WriteLine("Product Name = " + elem.ChildNodes[1].InnerText);
        Assert.AreEqual("3", elem.ChildNodes[2].InnerText);
    }
}

Output:

=========== GetRowFromElement ================
Product Name = Pavlova
Product Name = Teatime Chocolate Biscuits
Product Name = Sir Rodney's Marmalade
Product Name = Sir Rodney's Scones
Product Name = NuNuCa Nuß-Nougat-Creme
Product Name = Gumbär Gummibärchen
Product Name = Schoggi Schokolade
Product Name = Zaanse koeken
Product Name = Chocolade
Product Name = Maxilaku
Product Name = Valkoinen suklaa
Product Name = Tarte au sucre
Product Name = Scottish Longbreads
=========== GetElementFromRow ================
Product Name = Pavlova
Product Name = Teatime Chocolate Biscuits
Product Name = Sir Rodney's Marmalade
Product Name = Sir Rodney's Scones
Product Name = NuNuCa Nuß-Nougat-Creme
Product Name = Gumbär Gummibärchen
Product Name = Schoggi Schokolade
Product Name = Zaanse koeken
Product Name = Chocolade
Product Name = Maxilaku
Product Name = Valkoinen suklaa
Product Name = Tarte au sucre
Product Name = Scottish Longbreads

Directly generate HTML from DataSet with XSLT

It is quite attractive to convert the content in DataSet to variant output formats, such as HTML/XHTML, WAP, PDF, SVG etc. The .NET Framework provides perfect support for XSLT which facilitates such kinds of transformation. More information about XSLT can be found here.

Now, let�s suppose we want to generate a simple report about the order details of the ten most expensive products. Following the previous several sections, we can easily build a virtual nested XML document from the �Products� and �OrderDetails� tables. Then we create an XSTL file like this:

<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:template match="/">
<HTML>
  <BODY>
    <TABLE BORDER="0" cellspacing="3" cellpadding="8">
      <TR bgcolor="#FFCC00">
        <TD>Product Name</TD>
        <TD>Price</TD>
        <TD>Discount</TD>
        <TD>Quantity</TD>
        <TD>Total</TD>
      </TR>
      <xsl:apply-templates select="XmlDb/Products[position() < 10]">
        <xsl:sort select="UnitPrice" order="descending" data-type = "number" />
      </xsl:apply-templates>      
    </TABLE>
</BODY>
</HTML>
</xsl:template>
<xsl:decimal-format name="us" decimal-separator='.' grouping-separator=',' />
<xsl:template match="XmlDb/Products[position() < 10]">
<TR >
<xsl:if test="position() mod 2 = 0">
      <xsl:attribute name="bgcolor">#EEEEEE</xsl:attribute>
</xsl:if>
<xsl:if test="position() mod 2 = 1">
      <xsl:attribute name="bgcolor">#AAAAAA</xsl:attribute>
</xsl:if>
        <TD><xsl:value-of select="ProductName"/></TD>
        <TD><xsl:value-of select="format-number(UnitPrice, 
                                    '#.00', 'us')"/>$</TD>
<xsl:if test="number(OrderDetails/Discount) != 0">
        <TD>-<xsl:value-of select="number(OrderDetails/Discount)*100"/>%</TD>
</xsl:if>
<xsl:if test="number(OrderDetails/Discount) = 0">
        <TD>-----</TD>
</xsl:if>
       <TD><xsl:value-of select="OrderDetails/Quantity"/></TD>
        <TD><xsl:value-of 
              select="number(UnitPrice)*number(OrderDetails/Quantity)*
                      (1-number(OrderDetails/Discount))"/>$
        </TD>
      </TR>
      </xsl:template>
</xsl:stylesheet>

Now, write a simple function to perform the XSLT transformation:

[Test] public void GenerateHTMLFromXSLT() 
{
//Create an ODBC connection to the database. Here it is an Access file

    OdbcConnection conn = new OdbcConnection("DSN=XmlDb_NorthWind");

    //Create a DataSet with a name "XmlDb"

    DataSet dataset = new DataSet("XmlDb");

    //Load "Products" table from original data source to the DataSet

    OdbcDataAdapter adapter = new OdbcDataAdapter();
    adapter.SelectCommand = new OdbcCommand("SELECT * FROM Products", conn);
    adapter.Fill(dataset, "Products");

    //Load "Order Details" table from original data source to the DataSet

    adapter.SelectCommand = new OdbcCommand("SELECT * FROM [Order Details]", conn);
    adapter.Fill(dataset, "OrderDetails");

    //Create a relationship between the two tables

    dataset.Relations.Add(
                dataset.Tables["Products"].Columns["ProductID"],
                dataset.Tables["OrderDetails"].Columns["ProductID"]).Nested = true;;

    //Build a virtual XML document on top of the DataSet

    XmlDataDocument doc = new XmlDataDocument(dataset); 

    //Load the XSLT file. NOTE: Here it is compiled as an embedded resource file

    Assembly assembly = System.Reflection.Assembly.GetExecutingAssembly();
    XslTransform xslTran = new XslTransform();
    Stream xslStream = 
      assembly.GetManifestResourceStream("UnitTest_ADO.NET_XML.Test.xslt");
    XmlTextReader reader = new XmlTextReader(xslStream);
    xslTran.Load(reader, null, null);
            
    //Output the result a HTML file

    XmlTextWriter writer = new XmlTextWriter("xsltresult.html", 
                                      System.Text.Encoding.UTF8);
    xslTran.Transform(doc.CreateNavigator(), null, writer, null);
    writer.Close();
}

Output:

Figure 4: HTML Output

Extension: Advanced XPath Query

Standard XPath is not powerful enough to be a database query language, e.g.: it lacks the DateTime related functions. Fortunately, it could be enhanced with customized XPath functions. Mr. Prajakta Joshi has published a very comprehensive article on this topic in MSDN. But his approach is too complex to follow. You should manually point out the function name, arguments number and their types, return type for every customized function, etc. It is not so flexible to add new XPath functions and is hard to maintain. Now, let's use the Reflection mechanism in .NET Framework to simplify this process (refer to Figure 5).

Figure 5: Customize XPath functions

All customized functions can only present as static methods in the XmlDbXPathFunctions class. Once XPathExpress requires XsltContext to ResolveFunction(). The XsltContext creates an XmlDbXPathFunctionWrapper object which implements the IXsltContextFunction interface. A function name and required argument types will be passed to its constructor. In the constructor, XmlDbXPathFunctionWrapper tries to find a best-fit static method in the XmlDbXPathFunctions class. Then XPathExpression call the Invoke() method of XmlDbXPathFunctionWrapper, which will invoke the real correspondent method in the XmlDbXPathFunctions class and return the result.

This is a very flexible approach to extend the XPath. If you want to add your own function to XPath execution context, you just need to write a static method in the XmlDbXPathFunctions class. The new function will be detected automatically.

Some useful DateTime functions have already been added. Now you can extract the year, month, day, hour, minute, second, ticks form a DateTime XmlNode. It is quite helpful when you try to filter the XmlNode set with the DateTime information. For example: "//Orders[ex:year(string(ShippedDate)) = 1995 and ex:month(string(ShippedDate)) <= 3]" will seek all orders shipped in the first quarter in the year of 1995.

[Test] public void XPathExtension()
{
    //Create an ODBC connection to the database. Here it is an Access file

    OdbcConnection conn = new OdbcConnection("DSN=XmlDb_NorthWind");

    //Create a DataSet with a name "XmlDb"

    DataSet dataset = new DataSet("XmlDb");

    //Create a DataAdapter to load data from original data source to the DataSet

    OdbcDataAdapter adapter = new OdbcDataAdapter();
    adapter.SelectCommand = new OdbcCommand("SELECT * FROM Orders", conn);
    adapter.Fill(dataset, "Orders");

    //Create a virtual XML document on top of the DataSet

    XmlDataDocument doc = new XmlDataDocument(dataset); 

    //Create an XPath navigator

    XPathNavigator nav = doc.CreateNavigator();

    //XPath expression

    String xpath = 
      "//Orders[ex:year(ShippedDate)=1995 and ex:month(ShippedDate)<=3]";

    //Compile the XPath expression

    XPathExpression xpathexp = nav.Compile(xpath);

    //Assign a customized XPath context

    XmlDbXPathContext context = new XmlDbXPathContext(new NameTable());
    context.AddNamespace("ex", "http://openvue.net");
    xpathexp.SetContext(context);

    //Perform XPath query

    XPathNodeIterator it = nav.Select(xpathexp);

    //Output the result

    StringBuilder stringBuilder = new StringBuilder();
    stringBuilder.Append("<Results>");

    while (it.MoveNext())
    {
        XmlElement elem = (XmlElement)((IHasXmlNode)it.Current).GetNode();
        stringBuilder.Append(elem.ChildNodes[4].OuterXml);
        DateTime dt = Convert.ToDateTime(elem.ChildNodes[4].InnerText);
        Assert.AreEqual(1995, dt.Year);
        Assert.IsTrue(dt.Month <= 3);
    }
    stringBuilder.Append("</Results>");
    XmlDocument docResult = new XmlDocument();
    docResult.LoadXml(stringBuilder.ToString());
    docResult.Save(Console.Out);
}

Output:

<?xml version="1.0" encoding="Windows-1252"?>
<Results>
  <ShippedDate>1995-01-03T00:00:00.0000000+01:00</ShippedDate>
  <ShippedDate>1995-01-02T00:00:00.0000000+01:00</ShippedDate>
  <ShippedDate>1995-02-16T00:00:00.0000000+01:00</ShippedDate>
  <ShippedDate>1995-01-13T00:00:00.0000000+01:00</ShippedDate>
  <ShippedDate>1995-01-16T00:00:00.0000000+01:00</ShippedDate>
  <ShippedDate>1995-02-10T00:00:00.0000000+01:00</ShippedDate>
  <ShippedDate>1995-02-10T00:00:00.0000000+01:00</ShippedDate>
  <ShippedDate>1995-02-09T00:00:00.0000000+01:00</ShippedDate>
  <ShippedDate>1995-03-02T00:00:00.0000000+01:00</ShippedDate>
  <ShippedDate>1995-02-27T00:00:00.0000000+01:00</ShippedDate>
  <ShippedDate>1995-02-27T00:00:00.0000000+01:00</ShippedDate>
  <ShippedDate>1995-03-03T00:00:00.0000000+01:00</ShippedDate>
  <ShippedDate>1995-03-27T00:00:00.0000000+02:00</ShippedDate>
  <ShippedDate>1995-02-27T00:00:00.0000000+01:00</ShippedDate>
  <ShippedDate>1995-03-17T00:00:00.0000000+01:00</ShippedDate>
  <ShippedDate>1995-03-09T00:00:00.0000000+01:00</ShippedDate>
<ShippedDate>1995-03-31T00:00:00.0000000+02:00</ShippedDate>
��
</Results>

If you need some special XPath functions, just add them as static methods into the XmlDbXPathExtensionFunctions class. These static methods will be found automatically and be applied to your XPath query. Really simple, isn't it?

Conclusion

Treating a database as a large virtual XML document with ADO.NET is not always a good idea. It demands more memory, and its performance could be a big problem in the case of complex and enormous databases. But for relatively small projects, it works perfectly and might save a lot of time for you. Anyways, you could optimize the code above to make it meet your requirements.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here