Recipe 8.6 Using XPath to Query Data in a DataSet

Problem

You need to use an XPath expression to extract certain rows from a DataSet.

Solution

Use SelectSingleNode( ) or SelectNodes( ).

The sample code contains two event handlers:

Form.Load

Sets up the sample by creating a DataSet containing the Orders table and Order Details table from Northwind and a nested relation between the two tables.

Go Button.Click

Executes an XPath query to retrieve the Orders and Order Details data for an OrderID specified by the user to an XmlNode. The results are displayed by iterating over the XmlNode to retrieve the Orders and the XmlNodeList containing the Order Details.

The C# code is shown in Example 8-9.

Example 8-9. File: XPathQueryForm.cs

// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Windows.Forms;
using System.Text;
using System.Xml;
using System.Data;
using System.Data.SqlClient;

// Table name constants
private const String ORDERS_TABLE        = "Orders";
private const String ORDERDETAILS_TABLE  = "OrderDetails";

// Relation name constants
private const String ORDERS_ORDERDETAILS_RELATION =
    "Orders_OrderDetails_Relation";

// Field name constants
private const String ORDERID_FIELD       = "OrderID";

private DataSet ds;

//  . . . 

private void XPathQueryForm_Load(object sender, System.EventArgs e)
{
    ds = new DataSet("Orders_OrderDetails");    
    SqlDataAdapter da;

    // Fill the Order table and add it to the DataSet.
    da = new SqlDataAdapter("SELECT * FROM Orders",
        ConfigurationSettings.AppSettings["Sql_ConnectString"]);
    DataTable orderTable = new DataTable(ORDERS_TABLE);
    da.Fill(orderTable);
    ds.Tables.Add(orderTable);

    // Fill the OrderDetails table and add it to the DataSet.
    da = new SqlDataAdapter("SELECT * FROM [Order Details]",
        ConfigurationSettings.AppSettings["Sql_ConnectString"]);
    DataTable orderDetailTable = new DataTable(ORDERDETAILS_TABLE);
    da.Fill(orderDetailTable);
    ds.Tables.Add(orderDetailTable);

    // Create a relation between the tables.
    ds.Relations.Add(ORDERS_ORDERDETAILS_RELATION,
        ds.Tables[ORDERS_TABLE].Columns[ORDERID_FIELD],
        ds.Tables[ORDERDETAILS_TABLE].Columns[ORDERID_FIELD],
        true);

    ds.Relations[ORDERS_ORDERDETAILS_RELATION].Nested = true;
}

private void goButton_Click(object sender, System.EventArgs e)
{
    int orderId = 0;
    // Get the user-entered Order ID.
    try
    {
        orderId = Int32.Parse(orderIdTextBox.Text);
    }
    catch(Exception ex)
    {
        MessageBox.Show(ex.Message);
        return;
    }

    // Use an XPath query to select the order.
    String xPathQuery =
        "/Orders_OrderDetails/Orders[OrderID = " + orderId + "]";
    XmlNode xmlNode =
        (new XmlDataDocument(ds)).SelectSingleNode(xPathQuery);

    StringBuilder result = new StringBuilder( );

    if (xmlNode != null)
    {
        // Retrieve the query results for the Order.
        result.Append("OrderID = " +
            xmlNode.ChildNodes[0].InnerText + Environment.NewLine);
        result.Append("CustomerID = " +
            xmlNode.ChildNodes[1].InnerText + Environment.NewLine);
        result.Append("OrderDate = " +
            xmlNode.ChildNodes[3].InnerText + Environment.NewLine);
        result.Append("Line Items:" + Environment.NewLine);

        // Retrieve the query results for the Order Details.
        XmlNodeList xmlNodeList = xmlNode.SelectNodes("OrderDetails");
        for (int i = 0; i < xmlNodeList.Count; i++)
        {
            result.Append("\tProductID = " +
                xmlNodeList[i].ChildNodes[1].InnerText +
                Environment.NewLine);
            result.Append("\tQuantity = " +
                xmlNodeList[i].ChildNodes[2].InnerText +
                Environment.NewLine);
            result.Append("\tUnitPrice = " +
                xmlNodeList[i].ChildNodes[3].InnerText +
                Environment.NewLine + Environment.NewLine);
        }
    }
    else
    {
        result.Append("No data found for Order ID = " + orderId);
    }

    xPathQueryResultTextBox.Text = result.ToString( );
}

Discussion

The W3C XML Path Language (XPath) is a navigation language used to select nodes from an XML Document. It is defined by W3 as a standard navigation language. The specification can be found at http://www.w3.org/TR/xpath. Microsoft SQL Server 2000 implements a subset of the language as described in SQL Server Books Online under the topics "Guidelines for Using XPath Queries" and "Using XPath Queries."

In .NET, the DataSet is synchronized with the XmlDataDocument. As a result, in some cases XML services can be used to access the XmlDataDocument to perform certain functionality more conveniently than could be accomplished using the DataSet directly. To execute an XPath query against the contents of a DataSet, call the SelectSingleNode( ) method of the XmlDataDocument for the DataSet, passing the XPath query as an argument as shown in the example:

XmlNode xmlNode = (new XmlDataDocument(ds)).SelectSingleNode(xPathQuery);

The example iterates over the Order Details for the Orders by accessing the XmlNodeList containing that data within the XmlNode retrieved by the XPath query:

XmlNodeList xmlNodeList = xmlNode.SelectNodes("OrderDetails");

This works because the Nested property is set to true for the DataRelation relating the tables containing the Orders and Order Details data. If the Nested property were false, you'd have to use a second XPath query to retrieve the Order Details data from the XMLDataDocument for the DataSet.