Querying a Virtual XML Document
Before diving into the code, let's quickly look at the scenario I use for this column's example. Suppose your company employs an outside firm that runs special promotions for customers who place large orders. The outside firm requires you to send it an XML document that contains the orders a customer has placed. Each order includes an order identifier, the date the order was placed, and the total order amount along with information that uniquely identifies the customer.
To meet the firm's requirements, you need to develop a program to access your database, extract the data, and build the XML document. The example uses the Northwind sample database that comes with SQL Server. I use a DataSet to store the customer and order data from SQL Server and an XMLDataDocument to build a virtual XML document that contains the data. I also use an XPath query to select only customers who placed at least one order that totals more that $800—which is considered to be a large order. For brevity, I show only a subset of the query results. With this scenario as the goal, let's look at the code.
Listing 1 shows the C# code for this example. A single method, Main, contains the entire program. At callout A in Listing 1, the code creates a new DataSet, named CustOrder, and adds to CustOrder two DataTables, named Customer and Order. The names you use for the DataSet and DataTables correspond directly to element names in the virtual XML document, so you should choose the names carefully. In general, choose the singular form of a name instead of the plural (e.g., Customer instead of Customers) because the XML element represents a single Customer, not a set of Customers like the rows in a DataTable. Note the singular element names in Figure 1's XML view.
At callout B, the code creates and opens a connection to the Northwind database. Next, the code creates two SQLCommand objects containing queries that return a set of Customer rows and a set of Order rows from the database. Then, the code at callout C creates the DataAdapters, which use those queries to retrieve data and populate the two DataTables that the code at callout A creates.
After the DataAdapters have populated the DataTables, the code creates a DataRelation named CustOrder between the Customer and Order DataTables. The DataRelation specifies that the two tables be related through the CustomerID columns. A Nested property set to true causes the XMLData Document that the code at callout D creates to generate a virtual XML document that nests orders inside customers. The next line of code saves the entire contents of the virtual XML document to a file called result.xml, which Figure 1 shows.
Now that the XMLDataDocument is complete, you can use XPath to query the data. At callout E, the code uses the SelectNodes method on the XMLDataDocument to execute an XPath query that returns orders totaling more than $800. SelectNodes returns a list of nodes—in this case, elements—that match the query.
Next, the code creates an XmlTextWriter, which is an object that you use to write XML to a file. You want to make certain that the XML output is well formed; that is, that the XML has a single root element that contains all other elements. To ensure that outcome, first you use the XmlTextWriter to output a LargeOrders element. Then, you write each node returned from the XPath query to the output file by using the node's WriteTo method. WriteTo writes the XML representation of the node and all its descendants. Once you've written all the nodes to the output file, you close the root element and the XmlTextWriter. Figure 2 shows the resulting XML file.
.NET XML Query Results
The DataSet and XMLDataDocument classes are analogous to the SQL Server database and mapping schemas that programmers have used to provide XML views—and just as easy to use. However, unlike XML views, which have no object model, the DataSet and XMLDataDocument classes provide relational and hierarchical object models—the DataSet and the XML DOM, respectively—which you can use to manipulate the data or execute business logic.
Using a DataSet and XMLDataDocument to generate XML query results provides the added benefit of reducing load on your SQL Server by moving the creation of the XML query results to a middle-tier (or client) machine. However, you should carefully weigh the benefits of generating XML on the client or middle tier against the cost of retrieving large amounts of data from SQL Server into the DataSet's local cache. When you need to generate XML query results, execute business logic, or offload processing from your SQL Server machine, using .NET's XMLDataDocument might be your best—and most flexible—option.
End of Article
Prev. page
1
[2]
next page -->
