Location Paths
| A location path is an expression that selects a node set relative to the context node. You've already used these entities when you specified an XPath query inside of a template file or placed a query directly into an URL. This is just a formal name that we'll use from this point on. A location path can be broken down into one or more location steps separated by forward slashes . In multiple location step paths, the location steps are interpreted left to right. Each location step identifies a node set in the XML document tree model relative to the preceding location step. The node set produced by the last location step is the final result of the overall XPath expression. Location paths are composed of three different parts :
The general form of a location step is as follows : axis-identifier::node-test[predicate1][predicate2]. The axis identifier is separated from the node test with two colons (::), and each predicate, if any, is enclosed within square brackets ([]). Figure 6.1 illustrates the breakdown of an XPath expression into its component parts. Figure 6.1. The parts of an XPath expression. The following discussion might leave you wondering about the old "What came first, the chicken or the egg?" argument. That's really unavoidable because each of these three entities depends on each other to function and is somewhat defined in terms of each other. Let's look at each of these individual parts and include examples. We'll start with node tests. Specifying a Node TestThe node test is the main part of the XPath expression. This doesn't mean the other parts of the expression are not important. All I'm saying here is that the node test specifies the node type selected by the location step. It identifies the element or attribute that is the endpoint of the query. A node type is associated with each of the four axes (child, parent, attribute, and self), as shown in Table 6.6. Table 6.6. Principal Node Types for Each Axis
The wildcard node test ( parent: :* , for example) is not supported. Using Listing 6.1 to illustrate , if the location path specifies child::Orders , the <Orders> element children of the context node are selected. If the current context is ROOT , then the query results in a node set of three orders. This is shown graphically in Figure 6.2. Figure 6.2. Results of the child::Orders XPath expression. Listing 6.1 XML Document for Use in Node Test Examples<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <Orders OrderID="10248" CustomerID="VINET" OrderDate="1996-07- 04T00:00:00" /> <Orders OrderID="10249" CustomerID="TOMSP" OrderDate="1996-07- 05T00:00:00" /> <Orders OrderID="10250" CustomerID="HANAR" OrderDate="1996-07- 08T00:00:00" /> </ROOT> Here's another example. Assume the context node is the third Orders element in the sample code in Listing 6.1. If we use the XPath query attribute::OrderDate , the resulting node set is composed of the attribute node OrderDate (has the value 1996-07-08T00:00:00). This is shown graphically in Figure 6.3. Figure 6.3. Results of the attribute::OrderDate XPath expression. Because the child axis has <element> as its principal node type, the node test, Orders , is TRUE if Orders is an <element> node. Because the principal node type of the attribute axis is <attribute> , the node test is TRUE if CustomerID is an <attribute> node. If the context node doesn't have a CustomerID attribute, you'll get back an empty node set. In Microsoft's implementation of XPath, every XPath query begins from the ROOT context. As an example, a query beginning with Order is treated as /Order . In the query Order[Order Details] , Order begins at the root context, but Order Details begins at the Order context. Specifying AxesWhereas the node test specifies the node type selected by the location step, axes specify the tree relationship between the nodes selected by the location step and the context node. Parent, child, attribute, and self axes are supported in the Microsoft implementation. If a child axis is specified in the location path, all selected nodes are children of the context node. If a parent axis is specified, the selected node is the parent of the context node. If an attribute axis is specified, the selected nodes are attributes of the context node. Figure 6.4 shows a graphical representation of the relationships. Figure 6.4. Axes relationships in XPath expressions. The next four sections present examples of axis relationships in XPath queries. For these examples, we'll be using the XDR schema file shown in Listing 6.2. It relates the Customer, Order, Order Detail, and Employee tables of the Northwind database provided in Appendix A. We'll call this schema SampleSchema.xml and place it in our SchemaVirtualName directory. Listing 6.2 Sample XDR Schema File for Our Examples<?xml version="1.0" ?> <Schema xmlns="urn:schemas-microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:datatypes" xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <ElementType name="Customer" sql:relation="Customers"> <AttributeType name="CustomerID" dt:type="id" /> <AttributeType name="CompanyName" /> <AttributeType name="ContactName" /> <AttributeType name="City" /> <AttributeType name="Fax" /> <AttributeType name="Orders" dt:type="idrefs" sql:id-prefix="Ord-" /> <attribute type="CustomerID" /> <attribute type="CompanyName" /> <attribute type="ContactName" /> <attribute type="City" /> <attribute type="Fax" /> <attribute type="Orders" sql:relation="Orders" sql:field="OrderID"> <sql:relationship key-relation="Customers" key="CustomerID" foreign-relation="Orders" foreign-key="CustomerID" /> </attribute> <element type="Order"> <sql:relationship key-relation="Customers" key="CustomerID" foreign-relation="Orders" foreign-key="CustomerID" /> </element> </ElementType> <ElementType name="Order" sql:relation="Orders"> <AttributeType name="OrderID" dt:type="id" sql:id-prefix="Ord-" /> <AttributeType name="EmployeeID" /> <AttributeType name="CustomerID" /> <AttributeType name="OrderDate" /> <AttributeType name="RequiredDate" /> <AttributeType name="ShippedDate" /> <attribute type="OrderID" /> <attribute type="EmployeeID" /> <attribute type="CustomerID" /> <attribute type="OrderDate" /> <attribute type="RequiredDate" /> <attribute type="ShippedDate" /> <element type="OrderDetail"> <sql:relationship key-relation="Orders" key="OrderID" foreign-relation="[Order Details]" foreign-key="OrderID" /> </element> </ElementType> <ElementType name="OrderDetail" sql:relation="[Order Details]" sql:key-fields="OrderID ProductID"> <AttributeType name="ProductID" dt:type="idref" sql:id-prefix="Prod-" /> <AttributeType name="UnitPrice"/> <AttributeType name="Quantity" /> <attribute type="ProductID" /> <attribute type="UnitPrice" sql:field="UnitPrice" /> <attribute type="Quantity" /> <element type="Discount" sql:field="Discount"/> </ElementType> <ElementType name="Discount" dt:type="string" sql:relation="[Order Details]"/> <ElementType name="Employee" sql:relation="Employees"> <AttributeType name="EmployeeID" /> <AttributeType name="LastName" /> <AttributeType name="FirstName" /> <AttributeType name="Title" /> <attribute type="EmployeeID" /> <attribute type="LastName" /> <attribute type="FirstName" /> <attribute type="Title" /> </ElementType> </Schema> Child ElementsThis XPath query selects all the child <Customer> elements of the current context node: Child::Customer Listing 6.3 is a template file we'll use to test this expression. Place this file in the TemplateVirtualName directory. Listing 6.3 Template File to Test Child::Customer<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <sql:xpath-query mapping-schema="../schemas/SampleSchema.xml"> /child::Customer </sql:xpath-query> </ROOT> This template produces a rather large XML result file, a portion of which is given in Listing 6.4. Listing 6.4 Partial Result of Child::Customer<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <Customer CustomerID="ALFKI" CompanyName="Alfreds Futterkiste" ContactName="Maria Anders" City="Berlin" Fax="030-0076545" Orders="Ord-10643 Ord-10692 Ord-10702 Ord-10835 Ord-10952 Ord-11011"> <Order OrderID="Ord-10643" EmployeeID="6" OrderDate="1997-08- 25T00:00:00" CustomerID="ALFKI" RequiredDate="1997-09-22T00:00:00" ShippedDate="1997-09- 02T00:00:00"> <OrderDetail ProductID="Prod-28" UnitPrice="45.6" Quantity="15"> <Discount>0.25</Discount> </OrderDetail> <OrderDetail ProductID="Prod-39" UnitPrice="18" Quantity="21"> <Discount>0.25</Discount> </OrderDetail> <OrderDetail ProductID="Prod-46" UnitPrice="12" Quantity="2"> <Discount>0.25</Discount> </OrderDetail> </Order> <Order OrderID="Ord-10692" EmployeeID="4" OrderDate="1997-10- 03T00:00:00" CustomerID="ALFKI" RequiredDate="1997-10-31T00:00:00" ShippedDate="1997-10- 13T00:00:00"> <OrderDetail ProductID="Prod-63" UnitPrice="43.9" Quantity="20"> <Discount>0</Discount> </OrderDetail> </Order> ... We could have placed the query directly in the URL, like this: http://iisserver/Nwind/schema/SampleSchema.xml/child::Customer?root=root. This would have produced the same result as Listing 6.4. GrandchildrenLet's take the query one level lower and look for grandchildren elements. The XPath query will be child::order/child::OrderDetail . Listing 6.5 shows the template file, and Listing 6.6 shows the results. Listing 6.5 Template File for child::Order/child::OrderDetail<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <sql:xpath-query mapping-schema="../schemas/SampleSchema.xml"> /child::Order/child::OrderDetail </sql:xpath-query> </ROOT> Listing 6.6 Partial Result of child::Order/child::OrderDetail<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <OrderDetail ProductID="Prod-11" UnitPrice="14" Quantity="12"> <Discount>0</Discount> </OrderDetail> <OrderDetail ProductID="Prod-42" UnitPrice="9.8" Quantity="10"> <Discount>0</Discount> </OrderDetail> <OrderDetail ProductID="Prod-72" UnitPrice="34.8" Quantity="5"> <Discount>0</Discount> </OrderDetail> <OrderDetail ProductID="Prod-14" UnitPrice="18.6" Quantity="9"> <Discount>0</Discount> </OrderDetail> ... Notice that only the grandchildren elements of the context node (root) are returned along with their subelements. No Order information is included. That's exactly what we said we wanted in our query. Parent ".."We can use the parent axis in any manner similar to the following XPath query: /child::Customer/child::Order/child::OrderDetail[../@CustomerID="VINET"] This query should return the OrderDetail elements for all orders that have a CustomerID of "VINET" . Listing 6.7 shows the template file, and Listing 6.8 shows the results. Listing 6.7 Template File for the @ CustomerID = "VINET" XPath Query<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <sql:xpath-query mapping-schema="../schemas/SampleSchema.xml"> /child::Customer/child::Order/child::OrderDetail[../@CustomerID="VINET"] </sql:xpath-query> </ROOT> Listing 6.8 Results of the @ CustomerID = "VINET" XPath Query<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <OrderDetail ProductID="Prod-11" UnitPrice="14" Quantity="12"> <Discount>0</Discount> </OrderDetail> <OrderDetail ProductID="Prod-42" UnitPrice="9.8" Quantity="10"> <Discount>0</Discount> </OrderDetail> <OrderDetail ProductID="Prod-72" UnitPrice="34.8" Quantity="5"> <Discount>0</Discount> </OrderDetail> <OrderDetail ProductID="Prod-71" UnitPrice="17.2" Quantity="20"> <Discount>0</Discount> </OrderDetail> <OrderDetail ProductID="Prod-72" UnitPrice="27.8" Quantity="7"> <Discount>0</Discount> </OrderDetail> <OrderDetail ProductID="Prod-56" UnitPrice="30.4" Quantity="4"> <Discount>0</Discount> </OrderDetail> <OrderDetail ProductID="Prod-13" UnitPrice="6" Quantity="4"> <Discount>0</Discount> </OrderDetail> <OrderDetail ProductID="Prod-41" UnitPrice="9.65" Quantity="12"> <Discount>0</Discount> </OrderDetail> <OrderDetail ProductID="Prod-36" UnitPrice="19" Quantity="6"> <Discount>0</Discount> </OrderDetail> <OrderDetail ProductID="Prod-52" UnitPrice="7" Quantity="18"> <Discount>0</Discount> </OrderDetail> </ROOT> AttributesOur last example of axis declarations deals with attribute. Here's our XPath query: /child::Customer/child::Order[attribute::CustomerID="VICTE"]. This query will return all Orders elements that have a CustomerID attribute of "VICTE" . Listing 6.9 shows the testing template, and partial results are in Listing 6.10. Listing 6.9 Template File for the @ CustomerID = "VICTE" XPath Query<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <sql:xpath-query mapping-schema="../schemas/SampleSchema.xml"> /child::Customer/child::Order[attribute::CustomerID="VICTE"] </sql:xpath-query> </ROOT> Listing 6.10 Partial Results of the @ CustomerID = "VICTE" XPath Query<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <Order OrderID="Ord-10251" EmployeeID="3" OrderDate="1996-07- 08T00:00:00" CustomerID="VICTE" RequiredDate="1996-08-05T00:00:00" ShippedDate="1996-07- 15T00:00:00"> <OrderDetail ProductID="Prod-22" UnitPrice="16.8" Quantity="6"> <Discount>5.0000001E-2</Discount> </OrderDetail> <OrderDetail ProductID="Prod-57" UnitPrice="15.6" Quantity="15"> <Discount>5.0000001E-2</Discount> </OrderDetail> <OrderDetail ProductID="Prod-65" UnitPrice="16.8" Quantity="20"> <Discount>0</Discount> </OrderDetail> </Order> <Order OrderID="Ord-10334" EmployeeID="8" OrderDate="1996-10- 21T00:00:00" CustomerID="VICTE" RequiredDate="1996-11-18T00:00:00" ShippedDate="1996-10- 28T00:00:00"> <OrderDetail ProductID="Prod-52" UnitPrice="5.6" Quantity="8"> <Discount>0</Discount> </OrderDetail> <OrderDetail ProductID="Prod-68" UnitPrice="10" Quantity="10"> <Discount>0</Discount> </OrderDetail> </Order> ... Specifying Selection PredicatesIf you haven't already noticed, a selection predicate acts similarly to the WHERE clause in an SQL statement. It narrows down or filters a node set with respect to an axis. It is placed between brackets. For each node in the node set to which the selection predicate is applied, the selection predicate is evaluated with that node as the context node. If the selection predicate expression evaluates to TRUE for that node, the node is included in the resulting node set. To illustrate this filtering effect, we'll discuss the XPath expression (location path) child::Customer/child::Order[attribute::CustomerID="VICTE"]. First, all of the <Order> element children of the context node are selected and produce a node set. Second, the selection predicate test is applied to this node set, and all Order elements that do not have a CustomerID attribute with the value of "VICTE" are eliminated from the node set. So this XPath query returns only the <Order> element nodes having the attribute value "VICTE" for its CustomerID attribute. This discussion was illustrated in Listings 6.9 and 6.10. Working with Boolean PredicatesThere are selection pr times when multiple predicates will be necessary to obtain the result you want. After you start using more than one predicate, you enter the realm of Boolean filtering. This filtering effect can be implemented both implicitly and explicitly. The next three sections discuss how to implement these Boolean predicates. Implicit Boolean PredicatesLet's say I want an XPath query that returns all orders placed by Bottom-Dollar Markets that were handled by Janet Leverling. The following query would accomplish this: /child::Customer[attribute::CustomerID="BOTTM"]/child::Order [attribute::EmployeeID="3"]. We can test this by using the template file in Listing 6.11. Four orders will be returned. The first two are given in Listing 6.12. Listing 6.11 Template File for the CustomerID = "BOTTM" XPath Query<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <sql:xpath-query mapping-schema="../schemas/SampleSchema.xml"> /child::Customer[attribute::CustomerID="BOTTM"]/child::Order [attribute::EmployeeID="3"] </sql:xpath-query> </ROOT> Listing 6.12 Partial Results of CustomerID="BOTTM"<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <Order OrderID="Ord-10410" EmployeeID="3" OrderDate="1997-01- 10T00:00:00" CustomerID="BOTTM" RequiredDate="1997-02-07T00:00:00" ShippedDate="1997-01- 15T00:00:00"> <OrderDetail ProductID="Prod-33" UnitPrice="2" Quantity="49"> <Discount>0</Discount> </OrderDetail> <OrderDetail ProductID="Prod-59" UnitPrice="44" Quantity="16"> <Discount>0</Discount> </OrderDetail> </Order> <Order OrderID="Ord-10492" EmployeeID="3" OrderDate="1997-04- 01T00:00:00" CustomerID="BOTTM" RequiredDate="1997-04-29T00:00:00" ShippedDate="1997-04- 11T00:00:00"> <OrderDetail ProductID="Prod-25" UnitPrice="11.2" Quantity="60"> <Discount>5.0000001E-2</Discount> </OrderDetail> <OrderDetail ProductID="Prod-42" UnitPrice="11.2" Quantity="20"> <Discount>5.0000001E-2</Discount> </OrderDetail> </Order> ... This combination of predicates is one way of implicitly defining the Boolean and operator. Multiple attributes can also be used in a couple of other ways. They can be specified as successive predicates (one right after the other) and also as nested predicates (one inside the other). Here is an example of successive predicates: /child::Customer/child::Order[attribute::CustomerID="ERNSH"] [attribute::ShippedDate] This query, which returns a node set of all orders placed by the Ernst Handel company that have been shipped (read that as have a ShippedDate element ), should return 28 records. A test template file for this query is in Listing 6.13. A more logical query might be for all those orders that have not been shipped. We'll have an example of that in the "Boolean Functions" section later in this chapter. Partial results showing the first returned order are given in Listing 6.14. Listing 6.13 Template File for the CustomerID="ERNSH" XPath Query<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <sql:xpath-query mapping-schema="../schemas/SampleSchema.xml"> /child::Customer/child::Order[attribute::CustomerID="ERNSH"] [attribute::ShippedDate] </sql:xpath-query> </ROOT> Listing 6.14 Partial Results of the CustomerID="ERNSH" XPath Query<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <Order OrderID="Ord-10258" EmployeeID="1" OrderDate="1996-07- 17T00:00:00" CustomerID="ERNSH" RequiredDate="1996-08-14T00:00:00" ShippedDate="1996-07- 23T00:00:00"> <OrderDetail ProductID="Prod-2" UnitPrice="15.2" Quantity="50"> <Discount>0.2</Discount> </OrderDetail> <OrderDetail ProductID="Prod-32" UnitPrice="25.6" Quantity="6"> <Discount>0.2</Discount> </OrderDetail> <OrderDetail ProductID="Prod-5" UnitPrice="17" Quantity="65"> <Discount>0.2</Discount> </OrderDetail> </Order> ... Here is an example of nested predicates: child::Customer[child::Order[attribute::EmployeeID=3]] This produces a node set that contains any Orders elements with an EmployeeID attribute of 3 . Explicit Boolean Predicates Using OperatorsIn the preceding section, we saw the myriad of ways that exist to emulate the Boolean AND operator implicitly. This section discusses the use of explicit operator specification of both AND and OR . We'll stick with or because we haven't had examples of it so far. Because explicit specification is so straightforward, employing the or operator is a very simple process. We'll use the following query, which generates a node set of all orders placed by either the Vins et alcools Chevalier or the Toms Spezialita ¼ten company. /child::Customer/child::Order[attribute::CustomerID="VINET" or attribute::CustomerID="TOMSP"] Listing 6.15 shows the template that tests our XPath query, and Listing 6.16 shows the results. There should be 11 records returned. Listing 6.15 Template File for the CustomerID="VINET" XPath Query<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <sql:xpath-query mapping-schema="../schemas/SampleSchema.xml"> /child::Customer/child::Order[attribute::CustomerID="VINET" or attribute::CustomerID="TOMSP"] </sql:xpath-query> </ROOT> Listing 6.16 Partial Results of the CustomerID="VINET" XPath Query<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <Order OrderID="Ord-10249" EmployeeID="6" OrderDate="1996-07- 05T00:00:00" CustomerID="TOMSP" RequiredDate="1996-08-16T00:00:00" ShippedDate="1996-07- 10T00:00:00"> <OrderDetail ProductID="Prod-14" UnitPrice="18.6" Quantity="9"> <Discount>0</Discount> </OrderDetail> <OrderDetail ProductID="Prod-51" UnitPrice="42.4" Quantity="40"> <Discount>0</Discount> </OrderDetail> </Order> <Order OrderID="Ord-10438" EmployeeID="3" OrderDate="1997-02- 06T00:00:00" CustomerID="TOMSP" RequiredDate="1997-03-06T00:00:00" ShippedDate="1997-02- 14T00:00:00"> <OrderDetail ProductID="Prod-19" UnitPrice="7.3" Quantity="15"> <Discount>0.2</Discount> </OrderDetail> <OrderDetail ProductID="Prod-34" UnitPrice="11.2" Quantity="20"> <Discount>0.2</Discount> </OrderDetail> <OrderDetail ProductID="Prod-57" UnitPrice="15.6" Quantity="15"> <Discount>0.2</Discount> </OrderDetail> </Order> ... Boolean FunctionsThis section covers the Boolean functions not() , true() , and false() . The not() function enables you to reverse the conditions of the predicate test. Two sections ago in "Implicit Boolean Predicates," we used the following XPath query: /child::Customer/child::Order[attribute::CustomerID="ERNSH"] [attribute::ShippedDate] This query generated a node set composed of all Orders placed by the Ernst Handel company that have a ShippedDate element. I also told you that it would be more logical to search for orders that have not been shipped (keyword not ). To accomplish this, we would reverse the attribute::shipped predicate. The query would then become: /child::Customer/child::Order[attribute::CustomerID="ERNSH"] [not(attribute::ShippedDate)] This query produces two Order nodes, and the results are given in Listing 6.17. The template file doesn't change and is the same as the template given in Listing 6.13. Listing 6.17 Results of the CustomerID="ERNSH" XPath Query<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <Order OrderID="Ord-11008" EmployeeID="7" CustomerID="ERNSH" OrderDate="1998-04-08T00:00:00" RequiredDate="1998-05-06T00:00:00"> <OrderDetail ProductID="Prod-28" UnitPrice="45.6" Quantity="70"> <Discount>5.0000001E-2</Discount> </OrderDetail> <OrderDetail ProductID="Prod-34" UnitPrice="14" Quantity="90"> <Discount>5.0000001E-2</Discount> </OrderDetail> <OrderDetail ProductID="Prod-71" UnitPrice="21.5" Quantity="21"> <Discount>0</Discount> </OrderDetail> </Order> <Order OrderID="Ord-11072" EmployeeID="4" CustomerID="ERNSH" OrderDate="1998-05-05T00:00:00" RequiredDate="1998-06-02T00:00:00"> <OrderDetail ProductID="Prod-2" UnitPrice="19" Quantity="8"> <Discount>0</Discount> </OrderDetail> <OrderDetail ProductID="Prod-41" UnitPrice="9.65" Quantity="40"> <Discount>0</Discount> </OrderDetail> <OrderDetail ProductID="Prod-50" UnitPrice="16.25" Quantity="22"> <Discount>0</Discount> </OrderDetail> <OrderDetail ProductID="Prod-64" UnitPrice="33.25" Quantity="130"> <Discount>0</Discount> </OrderDetail> </Order> </ROOT> The true() and false() Boolean functions can be used to form queries that we've written previously. For example, the query that produced the results in Listing 6.17 could have been written as follows: /child::Customer/child::Order[attribute::CustomerID="ERNSH"] [attribute::ShippedDate = false()] The use of the Boolean true() function in the following query generates a node set of all customers that have placed at least one order: /child::Customer[child::Order = true()] Relational OperatorsSo far, we've written queries that test for an entity being equal to something, an entity that satisfies multiple equalities (Boolean and ), and several other types of predicate tests, but we haven't used the relational operators in any way. Just as a reminder, the relational operators are = , != , < , <= , > , >= . In the following query, we use the greater than ( > ) relational operator to demonstrate their use. From there, it is simple substitution to utilize the other operators. This XPath query generates a node set of all OrderDetail records that have a Quantity attribute greater than 2: /child::Customer/child::Order/child::OrderDetail[attribute::Quantity > 2] The template containing this query is Listing 6.18, and partial results are given in Listing 6.19. Listing 6.18 The Template File for the Quantity > 2 XPath Query<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <sql:xpath-query mapping-schema="../schemas/SampleSchema.xml"> /child::Customer/child::Order/child::OrderDetail [attribute::Quantity > 2] </sql:xpath-query> </ROOT> Listing 6.19 Partial Results of the Quantity > 2 XPath Query<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <OrderDetail ProductID="Prod-28" UnitPrice="45.6" Quantity="15"> <Discount>0.25</Discount> </OrderDetail> <OrderDetail ProductID="Prod-39" UnitPrice="18" Quantity="21"> <Discount>0.25</Discount> </OrderDetail> <OrderDetail ProductID="Prod-63" UnitPrice="43.9" Quantity="20"> <Discount>0</Discount> </OrderDetail> <OrderDetail ProductID="Prod-3" UnitPrice="10" Quantity="6"> <Discount>0</Discount> </OrderDetail> ... The relational operators < and <= will not work as written. You must substitute the entity encoding expression < ; for the < symbol in each instance. For example, you would rewrite the <= operator to <= (ugly but necessary). For some reason, I never seem to remember this little tidbit, and it's cost me development time. All you get for an error message is something like, "Sorry, I can't display that page," (wonderfully explanatory, don't you think?). Hopefully, it won't cost you. Arithmetic OperatorsUtilizing arithmetic operators in XPath expressions is just as straightforward as using the relational operators we discussed in the preceding section without the hiccup of the < operator. Listing 6.20 shows a sample query for determining when a customer spends more than $15,000 on an item. Listing 6.21 shows the result of Listing 6.20. /child::Customer/child::Order/child::OrderDetail[attribute:: Quantity * attribute::UnitPrice > 15000] Listing 6.20 Template File for the UnitPrice > 15000 XPath Query<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <sql:xpath-query mapping-schema="../schemas/SampleSchema.xml"> /child::Customer/child::Order/child::OrderDetail[attribute::Quantity * attribute::UnitPrice > 15000]] </sql:xpath-query> </ROOT> Listing 6.21 Results of the UnitPrice > 15000 XPath Query<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <OrderDetail ProductID="Prod-38" UnitPrice="263.5" Quantity="60"> <Discount>0</Discount> </OrderDetail> <OrderDetail ProductID="Prod-38" UnitPrice="263.5" Quantity="60"> <Discount>5.0000001E-2</Discount> </OrderDetail> </ROOT> Specifying Explicit Conversion FunctionsIt's time to talk about the explicit conversion functions string() and number() . We'll discuss the latter first. The number() function is used to convert a nonnumeric value to a numeric one. If the database you're using was originally designed correctly, then generally these functions are rarely used. By designed correctly, I mean that if a column will not be used in some type of math calculation, define it as a string value. If it will be used in a math calculation, define it as one of the numeric data types. Here's an example utilizing the string() function. The XPath query /Customer/Order[string(@EmployeeID)="6"] selects all Orders belonging to the employee with EmployeeID equal to 6 . The EmployeeID value is an integer, so to compare it to a string, it must first be converted before the comparison is made. The template file is given in Listing 6.22, with the partial results given in Listing 6.23. Listing 6.22 Template file for string(@OrderID)="Ord-10248"<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <sql:xpath-query mapping-schema="../schemas/SampleSchema.xml"> /child::Customer/child::Order[string(@OrderID)="Ord-10248"] </sql:xpath-query> </ROOT> Listing 6.23 Partial Results of string(@OrderID)="Ord-10248"<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <Order OrderID="Ord-10248" EmployeeID="5" CustomerID="VINET" OrderDate="1996-07-04T00:00:00" RequiredDate="1996-08-01T00:00:00" ShippedDate="1996-07-16T00:00:00"> <OrderDetail ProductID="Prod-11" UnitPrice="14" Quantity="12"> <Discount>0</Discount> </OrderDetail> <OrderDetail ProductID="Prod-42" UnitPrice="9.8" Quantity="10"> <Discount>0</Discount> </OrderDetail> <OrderDetail ProductID="Prod-72" UnitPrice="34.8" Quantity="5"> <Discount>0</Discount> </OrderDetail> </Order> </ROOT> Specifying XPath VariablesIn the section "Using XML Templates" in Chapter 4, I briefly showed the structure of a template file that utilized an XPath query to determine the resulting node set. Because we had not talked about XPath queries at that time, I wanted to hold off on discussing their use in template files. We will now discuss using XPath queries in template files. XPath variables and setting default values for them is very similar to what we discussed previously when we talked about parameter passing to template files. Listing 6.24 shows a template file with two variable values, one for EmployeeID and the other for CustomerID . The EmployeeID has a default value of 5 supplied, and VINET is supplied as the default value for the CustomerID . Listing 6.25 shows the results of executing this query without specifying values for the two quantities , thereby employing the default values. Listing 6.24 Template File with XPath Variables<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <sql:header> <sql:param name='EmployeeID'>5</sql:param> <sql:param name='CustomerID'>VINET</sql:param> </sql:header> <sql:xpath-query mapping-schema="../schemas/SampleSchema.xml"> /child::Customer/child::Order[attribute::CustomerID=$CustomerID and attribute::EmployeeID=$EmployeeID] </sql:xpath-query > </ROOT> Listing 6.25 Default Results of Template Execution<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <Order OrderID="Ord-10248" EmployeeID="5" OrderDate="1996-07- 04T00:00:00" CustomerID="VINET" RequiredDate="1996-08-01T00:00:00" ShippedDate="1996-07- 16T00:00:00"> <OrderDetail ProductID="Prod-11" UnitPrice="14" Quantity="12"> <Discount>0</Discount> </OrderDetail> <OrderDetail ProductID="Prod-42" UnitPrice="9.8" Quantity="10"> <Discount>0</Discount> </OrderDetail> <OrderDetail ProductID="Prod-72" UnitPrice="34.8" Quantity="5"> <Discount>0</Discount> </OrderDetail> </Order> </ROOT> Listing 6.26 shows the results after specifying the value of 6 for the EmployeeID and TOMSP for the CustomerID in the following URL: http://iisserver/Nwind/templates/template.xml?EmployeeID=6&CustomerID=TOMSP Listing 6.26 Default Results of Template Execution<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <Order OrderID="Ord-10249" EmployeeID="6" OrderDate="1996-07-05T00:00:00" CustomerID="TOMSP" RequiredDate="1996-08-16T00:00:00" ShippedDate="1996-07- 10T00:00:00"> <OrderDetail ProductID="Prod-14" UnitPrice="18.6" Quantity="9"> <Discount>0</Discount> </OrderDetail> <OrderDetail ProductID="Prod-51" UnitPrice="42.4" Quantity="40"> <Discount>0</Discount> </OrderDetail> </Order> <Order OrderID="Ord-10446" EmployeeID="6" OrderDate="1997-02-14T00:00:00" CustomerID="TOMSP" RequiredDate="1997-03-14T00:00:00" ShippedDate="1997-02- 19T00:00:00"> <OrderDetail ProductID="Prod-19" UnitPrice="7.3" Quantity="12"> <Discount>0.1</Discount> </OrderDetail> <OrderDetail ProductID="Prod-24" UnitPrice="3.6" Quantity="20"> <Discount>0.1</Discount> </OrderDetail> <OrderDetail ProductID="Prod-31" UnitPrice="10" Quantity="3"> <Discount>0.1</Discount> </OrderDetail> <OrderDetail ProductID="Prod-52" UnitPrice="5.6" Quantity="15"> <Discount>0.1</Discount> </OrderDetail> </Order> </ROOT> |
