Performing an XPath Query on a DataSet

The relationship that exists between a synchronized DataSet and XmlDataDocument provides you with XML services, such as the XML Path Language (XPath) query, that access the XmlDataDocument and can perform certain functionality more conveniently than if you were to access the DataSet directly. For example, rather than using the Select method of a DataTable to navigate relationships to other tables in a DataSet, you can perform an XPath query on an XmlDataDocument that is synchronized with the DataSet, to get a list of XML elements in the form of an XmlNodeList. The nodes in the XmlNodeList, cast as XmlElement nodes, can then be passed to the GetRowFromElement method of the XmlDataDocument, to return matching DataRow references to the rows of the table in the synchronized DataSet.

For example, the following code sample performs a "grandchild" XPath query. The DataSet is filled with three tables: Customers, Orders, and OrderDetails. In the sample, a parent-child relation is first created between the Customers and Orders tables, and between the Orders and OrderDetails tables. An XPath query is then performed to return an XmlNodeList of Customers nodes where a grandchild OrderDetails node has a ProductID node with the value of 43. In essence, the sample is using the XPath query to determine which customers have ordered the product that has the ProductID of 43.

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Xml

Public Class Sample
  Public Shared Sub Main()
    Dim nwindConn As SqlConnection = New SqlConnection("Data Source=localhost;Initial Catalog=northwind;Integrated Security=SSPI")
    nwindConn.Open()

    Dim myDataSet As DataSet = New DataSet("CustomerOrders")

    Dim custDA As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM Customers", nwindConn)
    custDA.Fill(myDataSet, "Customers")

    Dim ordersDA As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM Orders", nwindConn)
    ordersDA.Fill(myDataSet, "Orders")

    Dim detailsDA As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM [Order Details]", nwindConn)
    detailsDA.Fill(myDataSet, "OrderDetails")

    nwindConn.Close()

    myDataSet.Relations.Add("CustOrders", _
                            myDataSet.Tables("Customers").Columns("CustomerID"), _
                            myDataSet.Tables("Orders").Columns("CustomerID")).Nested = true

    myDataSet.Relations.Add("OrderDetail", _
                            myDataSet.Tables("Orders").Columns("OrderID"), _
                            myDataSet.Tables("OrderDetails").Columns("OrderID"), false).Nested = true

    Dim xmlDoc As XmlDataDocument = New XmlDataDocument(myDataSet) 
      
    Dim nodeList As XmlNodeList = xmlDoc.DocumentElement.SelectNodes("descendant::Customers[*/OrderDetails/ProductID=43]")
      
    Dim myRow As DataRow
    Dim myNode As XmlNode

    For Each myNode In nodeList
      myRow = xmlDoc.GetRowFromElement(CType(myNode, XmlElement))

      If Not myRow Is Nothing then Console.WriteLine(myRow(0).ToString())
    Next
  End Sub
End Class
[C#]
using System;
using System.Data;
using System.Data.SqlClient;
using System.Xml;

public class Sample
{
  public static void Main()
  {
    SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Initial Catalog=northwind;Integrated Security=SSPI;");
    nwindConn.Open();

    DataSet myDataSet = new DataSet("CustomerOrders");

    SqlDataAdapter custDA = new SqlDataAdapter("SELECT * FROM Customers", nwindConn);
    custDA.Fill(myDataSet, "Customers");

    SqlDataAdapter ordersDA = new SqlDataAdapter("SELECT * FROM Orders", nwindConn);
    ordersDA.Fill(myDataSet, "Orders");

    SqlDataAdapter detailsDA = new SqlDataAdapter("SELECT * FROM [Order Details]", nwindConn);
    detailsDA.Fill(myDataSet, "OrderDetails");

    nwindConn.Close();

    myDataSet.Relations.Add("CustOrders",
                         myDataSet.Tables["Customers"].Columns["CustomerID"],
                         myDataSet.Tables["Orders"].Columns["CustomerID"]).Nested = true;

    myDataSet.Relations.Add("OrderDetail",
                         myDataSet.Tables["Orders"].Columns["OrderID"],
                         myDataSet.Tables["OrderDetails"].Columns["OrderID"], false).Nested = true;

    XmlDataDocument xmlDoc = new XmlDataDocument(myDataSet); 
      
    XmlNodeList nodeList = xmlDoc.DocumentElement.SelectNodes("descendant::Customers[*/OrderDetails/ProductID=43]");
      
    DataRow myRow;
    foreach (XmlNode myNode in nodeList)
    {
      myRow = xmlDoc.GetRowFromElement((XmlElement)myNode);
      if (myRow != null)
        Console.WriteLine(myRow[0]);
    }
  }
}

See Also

Synchronizing a DataSet with an XmlDataDocument