XPath Data Types
Microsoft® SQL Server™ 2000, XPath, and XDR (XML-Data Reduced) have very different data types. For example, XPath does not have integer or date data types, but SQL Server and XDR have many. XDR uses nanosecond precision for time values, and SQL Server uses at most 1/300-second precision. Consequently, mapping one data type to another is not always possible. For more information about mapping SQL Server data types to XDR data types, see Data Type Coercions.
XPath has three data types: string, number, and boolean. The number data type is always an IEEE 754 double-precision floating-point. The SQL Server float(53) data type is the closest to XPath number. However, float(53) is not exactly IEEE 754. For example, neither NaN (Not-a-Number) nor infinity is used. Attempting to convert a nonnumeric string to number and trying to divide by zero results in an error.
XPath Conversions
When you use an XPath query such as OrderDetail[@UnitPrice > "10.0"], implicit and explicit data type conversions can change the meaning of the query in subtle ways. Therefore, it is important to understand how XPath data types are implemented. The XPath language specification, XML Path Language (XPath) version 1.0 W3C Proposed Recommendation 8 October 1999, can be found at the W3C Web site at http://www.w3.org/TR/1999/PR-xpath-19991008.html.
XPath operators are divided into four categories:
- Boolean operators (and, or)
- Relational operators (<, >, <=, >=)
- Equality operators (=, !=)
- Arithmetic operators (+, -, *, div, mod)
Each category of operator converts its operands differently. XPath operators implicitly convert their operands if necessary. Arithmetic operators convert their operands to number, and result in a number value. Boolean operators convert their operands to boolean, and result in a Boolean value. Relational operators and equality operators result in a Boolean value. However, they have different conversion rules depending on the original data types of their operands, as shown in this table.
| Operand | Relational operator | Equality operator |
| Both operands are node-sets | TRUE if and only if there is a node in one set and a node in the second set such that the comparison of their string values is TRUE. | Same |
| One is a node-set, the other a string | TRUE if and only if there is a node in the node-set such that when converted to number, the comparison of it with the string converted to number is TRUE. | TRUE if and only if there is a node in the node-set such that when converted to string, the comparison of it with the string is TRUE. |
| One is a node-set, the other a number | TRUE if and only if there is a node in the node-set such that when converted to number, the comparison of it with the number is TRUE. | Same |
| One is a node-set, the other a boolean | TRUE if and only if there is a node in the node-set such that when converted to boolean and then to number, the comparison of it with the boolean converted to number is TRUE. | TRUE if and only if there is a node in the node-set such that when converted to boolean, the comparison of it with the boolean is TRUE. |
| Neither is a node-set | Convert both operands to number and then compare. | Convert both operands to a common type and then compare. Convert to boolean if either is boolean, number if either is number; otherwise, convert to string. |
Note Because XPath relational operators always convert their operands to number, string comparisons are not possible. To include date comparisons, SQL Server 2000 offers this variation to the XPath specification: When a relational operator compares a string to a string, a node-set to a string, or a string-valued node-set to a string-valued node-set, a string comparison (not a number comparison) is performed.
Node-Set Conversions
Node-set conversions are sometimes nonintuitive. A node-set is converted to a string by taking the string value of only the first node in the set. A node-set is converted to number by converting it to string, and then converting string to number. A node-set is converted to boolean by testing for its existence.
Note Because SQL Server 2000 does not perform positional selection (for example, the XPath query Customer[3] means the third customer. This type of positional selection is not supported in SQL Server 2000.) on node-sets, the node-set-to-string or node-set-to-number conversions as described by the XPath specification are not implemented. SQL Server 2000 uses "any" semantics wherever the XPath specification specifies "first" semantics. For example, based on the W3C XPath specification, this XPath query Order[OrderDetail/@UnitPrice > 10.0] selects those orders with the first OrderDetail that has a UnitPrice greater than 10.0. In SQL Server 2000, this XPath query selects those orders with any OrderDetail that has a UnitPrice that is greater than 10.0.
Conversion to boolean generates an existence test; therefore, the XPath query Products[@Discontinued=true()] is equivalent to the SQL expression "Products.Discontinued is not null", not the SQL expression "Products.Discontinued = 1". To get the latter meaning, first convert the node-set to a non-boolean type, such as number. For example, Products[number(@Discontinued) = true()].
Because most operators are defined to be TRUE if they are TRUE for any or one of the nodes in the node-set, these operations always evaluate to FALSE if the node-set is empty. Thus, if A is empty, both A = B and A != B are FALSE, and not(A=B) and not(A!=B) are TRUE.
Usually, an attribute or element that maps to a column exists if the value of that column in the database is not null. Elements that map to rows exist if any of their children exist. For more information see, Using sql:relation and Using sql:field.
Note Elements annotated with is-constant always exist. Consequently, XPath predicates cannot be used on is-constant elements. For more information, see Creating Constant Elements Using sql:is-constant.
When a node-set is converted to string or number, its XDR type (if any) is inspected in the annotated schema and that type is used to determine the conversion that is required.
Mapping XDR Data Types to XPath Data Types
The XPath data type of a node is derived from the XDR data type in the schema, as shown in this table (the node EmployeeID is used for illustrative purpose).
XDR data type |
Equivalent XPath data type |
SQL Server conversion used |
|---|---|---|
| None bin.base64 bin.hex |
N/A | None EmployeeID |
| boolean | boolean | CONVERT(bit, EmployeeID) |
| number, int, float, i1, i2, i4, i8, r4, r8 ui1, ui2, ui4, ui8 |
number | CONVERT(float(53), EmployeeID) |
| id, idref, idrefs entity, entities enumeration notation nmtoken, nmtokens char dateTime dateTime.tz string uri uuid |
string | CONVERT(nvarchar(4000), EmployeeID, 126) |
| fixed14.4 | N/A (there is no data type in XPath that is equivalent to the fixed14.4 XDR data type) | CONVERT(money, EmployeeID) |
| date | string | LEFT(CONVERT(nvarchar(4000), EmployeeID, 126), 10) |
| time
time.tz |
string | SUBSTRING(CONVERT(nvarchar(4000), EmployeeID, 126), 1 + CHARINDEX(N'T', CONVERT(nvarchar(4000), EmployeeID, 126)), 24) |
The date and time conversions are designed to work whether the value is stored in the database using the SQL Server datetime data type or a string. Note that the SQL Server datetime data type does not use timezone and has a smaller precision than the XML time data type. To include the timezone data type or additional precision, store the data in SQL Server 2000 using a string type.
When a node is converted from its XDR data type to the XPath data type, additional conversion is sometimes necessary (from one XPath data type to another XPath data type). For example, consider this XPath query:
(@m + 3) = 4
If @m is of the fixed14.4 XDR data type, the conversion from XDR data type to XPath data type is accomplished using:
CONVERT(money, m)
In this conversion, the node m is converted from fixed14.4 to money. However, adding the value of 3, requires additional conversion:
CONVERT(float(CONVERT(money, m))
The XPath expression is evaluated as:
CONVERT(float(CONVERT(money, m)) + CONVERT(float(53), 3) = CONVERT(float(53), 3)
As shown in the following table, this is the same conversion that is applied for other XPath expressions (such as literals or compound expressions).
| X is unknown | X is string | X is number | X is boolean | |
| string(X) | CONVERT (nvarchar (4000), X, 126) |
- | CONVERT (nvarchar (4000), X, 126) |
CASE WHEN X THEN N'true' ELSE N'false' END |
| number(X) | CONVERT (float(53), X) |
CONVERT (float(53), X) |
- | CASE WHEN X THEN 1 ELSE 0 END |
| boolean(X) | - | LEN(X) > 0 | X != 0 | - |
Examples
A. Convert a data type in an XPath query
In the following XPath query specified against an annotated XDR schema, the query selects all the Employee nodes with the EmployeeID attribute value of E-1, where "E-" is the prefix specified using the sql:id-prefix annotation.
Employee[@EmployeeID="E-1"]
The predicate in the query is equivalent to the SQL expression:
N'E-' + CONVERT(nvarchar(4000), Employees.EmployeeID, 126) = N'E-1'
Because EmployeeID is one of the id (idref, idrefs, nmtoken, nmtokens, and so on) data type values in the XDR schema, EmployeeID is converted to the string XPath data type using the conversion rules described previously.
CONVERT(nvarchar(4000), Employees.EmployeeID, 126)
The "E-" prefix is added to the string, and the result is then compared with N'E-1'.
B. Perform several data type conversions in an XPath query
Consider this XPath query specified against an annotated XDR schema: OrderDetail[@UnitPrice * @Quantity > 98]
This XPath query returns all the <OrderDetail> elements satisfying the predicate @UnitPrice * @Quantity > 98. If the UnitPrice is annotated with a fixed14.4 data type in the annotated schema, this predicate is equivalent to the SQL expression:
CONVERT(float(53), CONVERT(money, [Order Details].UnitPrice)) * CONVERT(float(53), [Order Details].Quantity) > CONVERT(float(53), 98)
In converting the values in the XPath query, the first conversion converts the XDR data type to the XPath data type. Because the XDR data type of UnitPrice is fixed14.4, as described in the previous table, this is the first conversion that is used:
CONVERT(money, [Order Details].UnitPrice))
Because the arithmetic operators convert their operands to the number XPath data type, the second conversion (from one XPath data type to another XPath data type) is applied in which the value is converted to float(53) (float(53) is close to the XPath number data type):
CONVERT(float(53), CONVERT(money, [Order Details].UnitPrice))
Assuming the Quantity attribute has no XDR data type, Quantity is converted to a number XPath data type in a single conversion:
CONVERT(float(53), [Order Details].Quantity)
Similarly, the value 98 is converted to the number XPath data type:
CONVERT(float(53), 98)
Note If the XDR data type used in the schema is incompatible with the underlying SQL Server data type in the database, or if an impossible XPath data type conversion is performed, SQL Server may return an error. For example, if EmployeeID attribute is annotated with id-prefix annotation, the XPath Employee[@EmployeeID=1] generates an error because EmployeeID has the id-prefix annotation and cannot be converted to number.