ADO.NET 3.5 Cookbook, 2nd Edition

9.8. Using an XPath Query to Retrieve Data

Problem

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

Solution

Use the SelectSingleNode() and the SelectNodes() method of the XmlDataDocument class.

The solution creates a DataSet and fills it with schema and a subset of data from the Sales.SalesOrderHeader and Sales.SalesOrderDetail tables in AdventureWorks. A nested data relation is created between the tables. An XPath query is used to select the node for the SalesOrderHeader where the SalesOrderID = 43664 using the SelectSingleNode() method of the XmlDataDocument class for the DataSet. The related SalesOrderDetail nodes are selected from the XmlDataDocument using the SelectNodes() method of the XmlDataDocument. The query results are output to the console.

The C# code in Program.cs in the project XPathQueryDataSet is shown in Example 9-14.

Example 9-14. File: Program.cs for XPathQueryDataSet solution

using System;
using System.Data;
using System.Data.SqlClient;
using System.Xml;

namespace XPathQueryDataSet
{
    class Program
    {
        static void Main(string[] args)
        {
            int salesOrderId = 43664;

            string sqlConnectString = "Data Source=(local);" + "Integrated security=SSPI;Initial Catalog=AdventureWorks;"; string sqlSelect = "SELECT * FROM Sales.SalesOrderHeader " + "WHERE SalesOrderID < 43670;" + "SELECT * FROM Sales.SalesOrderDetail " + "WHERE SalesOrderID < 43670;"; // Create and fill the DataSet SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString); da.TableMappings.Add("Table", ...