XPath Conversions | XML and SQL Server 2000

Explicit conversions are conversions that a developer writes that utilize functions or other methods to change one entity to another of a different type. The important point is that the developer explicitly states what he wants to occur. Implicit conversions are those that occur when an application changes an entity from one data type to another with little or no input from the developer. These conversions of data types can often change a query meaning in ways we are not expecting. So it is of utmost importance that we understand exactly how XPath data types change under different circumstances.

Some discussions of XPath data types include the equality operators equal (=) and not equal (!=) with relational operators such as greater than (>). XPath operators are split into four different categories:

Arithmetic operators (+, -, *, div)
Equality operators (=, !=)
Relational operators (>, <, <=, >=)
Boolean operators (AND, OR)

All four of these categories convert operands in their own way. The arithmetic operators convert their operands to numbers and provide a number result. Boolean operators convert their operands to Boolean values and provide a Boolean result. However, relational and equality operator results depend heavily on the original data type of their operands even though they result eventually in a Boolean value. Table 6.3 shows how relational and equality operators are interpreted to provide their respective Boolean results based on the type of operands compared.

Table 6.3. Conversion Rules for Relational and Equality Operators

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 with the Boolean is TRUE.	TRUE if and only if there is a node in the node set such that when converted to Boolean, the compari son of to Boolean and then to number, the comparison of it with the Boolean converted to number 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 a string

If you examine Table 6.3 closely, you'll see that XPath string comparisons are not possible. Operands are always converted to the number data type. SQL Server bends the rules a little bit for date comparisons. In these cases, a string comparison is done.

Node Set Conversions

Converting node sets is not exactly the most instinctive process we'll come across. They don't quite work the way you would expect.

To convert a node set to a string, take the first node's string value. To convert to a number, first convert to a string and then convert that string to a number. When one of these conversions to a string or a number takes place, the annotated schema's XDR type determines the proper conversion to use.

To convert a node set to Boolean, just test for its existence. The general rule of thumb here is that an attribute or element that maps to a column exists as long as that column is not null in the database. Elements that map to rows exist if any of their children exist.

There are some peculiarities of Boolean conversions, however. An example is a test made on a bit (Boolean) field. The Products table (refer to Northwind example in Appendix A) is the only table with a bit field, so we'll use that as an example. The XPath query Products[@Discontinued=true()] is the same thing as the SQL statement: Discontinued is not null instead of the SQL statement: Discontinued = 1 as you would expect. If you wanted this last expression, first convert the node set to either a string or number like this: Products[string(@Discontinued) = true()] . Another thing to look out for is that most operators evaluate as false on empty node sets (a good way to test for an empty node set, don't you think?). This means that if X and Y are node sets and X is empty, then both X = Y and X != Y evaluate to false, and not( A = B ) and not( A != B ) both evaluate to true.

SQL Server's CONVERT Function

The SQL Server CONVERT function is used extensively when mapping XDR data types to XPath data types. The syntax for the SQL Server CONVERT function is as follows :

 CONVERT(data_type[(length)], expression[, style]).

The various parts of this function in order are as follows:

data_type . The target system-supplied data type, including bigint and sql_variant . User-defined data types cannot be used.
length . An optional parameter of nchar , nvarchar , char , varchar , binary , or varbinary data types.
expression . Any valid Microsoft SQL Server expression.
style . The style of date format used to convert datetime or smalldatetime data to character data ( nchar , nvarchar , char , varchar , nchar , or nvarchar data types), or the string format when converting float , real , money , or smallmoney data to character data ( nchar , nvarchar , char , varchar , nchar , or nvarchar data types). The style used is exclusively style 126.

Style 126 is designed for XML use. For conversion from datetime or smalldatetime to character data, the output format is as described in the table. For conversion from float, money, or smallmoney to character data, the output is equivalent to style 2. For conversion from real to character data, the output is equivalent to style 1.

Table 6.4 shows just how often the CONVERT function is used for data type mapping purposes.

For much more detailed information on the CONVERT function, refer to the SQL Books documentation that accompanies SQL Server 2000. Search for the topic "CAST and CONVERT."

Mapping XDR Data Types to XPath Data Types

Table 6.4 shows that a node's XDR schema data type determines its data type.

Table 6.4. Converting XDR Data Types to XPath Data Types

XDR Data Type	Equivalent XPath Data Type	SQL Server Conversion Used
`None` `bin.base64` `bin.hex` `Boolean`	N/A `Boolean`	None `CustomerID` `CONVERT(bit, CustomerID)`
`number, int, float ,i1, i2, i4, i8,r4, r8, ui1, ui2, ui4, ui8`	`number`	`CONVERT(float(53), CustomerID)`
`id, idref , idrefs` , `entity, entities, enumeration, notation, nmtoken, nmtokens, char, dateTime, dateTime.tz, string, uri, uuid`	`string`	`CONVERT(nvarchar(4000), CustomerID, 126)`
`fixed14.4`	N/A (there is no data type in XPath that is equivalent to the `fixed 14.4` XDR data type)	`CONVERT(money, CustomerID)`
`date`	`string`	`LEFT(CONVERT(nvarchar(4000), CustomerID, 126), 10)`
`time, time.tz`	`string`	`SUBSTRING(CONVERT(nvarchar(4000)` , `CustomerID, 126), 1 + CHARINDEX(N'T'` , `CONVERT(nvarchar(4000), CustomerID, 126)), 24)`

Let's look at the following example of how we can use Table 6.4 to understand exactly what conversions actually take place in XPath queries:

From a developer's point of view, it doesn't get any easier than a simple assignment statement, or so it appears. With an XPath expression of @Quantity = 14 , where Quantity is of data type fixed14.4 and 14 is of data type int , a couple of conversions must take place before the expression can be evaluated.

First, according to Table 6.4, the Quantity entity is converted to type money with the conversion CONVERT(money, Quantity) . Second, according to Table 6.4, the int data type 14 is converted to data type float with the conversion CONVERT(float(53), 14) . Done yet? Nope. We still have two different data types, a money and a float. The last step, then, is to explicitly convert the money data type to a float data type using the float conversion function supplied by SQL Server 2000. The final conversion performed is as follows:

 CONVERT(float(53), (CONVERT(money, Quantity))) + CONVERT(float(53), 14)

Table 6.5 shows additional conversions performed.

Table 6.5. Explicit Conversion Functions

	X is unknown	X is string	X is number	X is Boolean
`string(X)`	`CONVERT (nvarchar , (4000) X, 126)`	n/a	`CONVERT (nvarchar (4000), X, 126)`	`CASE WHEN X THEN true ELSE false END`
`number(X)`	`CONVERT (float(53), X)`	`CONVERT (float(53), X)`	n/a	`CASE WHEN X THEN 1 ELSE 0 END`
`Boolean(X)`	n/a	`LEN(X) > 0`	`X != 0`	n/a

Let's do one more example. This time we'll evaluate the XPath expression @UnitPrice * @Quantity > 65 . Here, UnitPrice is again of data type fixed14.4, 65 is of data type int, and Quantity is of data type smallint.

UnitPrice is converted to type money via CONVERT(money, UnitPrice) . Quantity is converted to type float via CONVERT(float(53), Quantity) , and the smallint 65 is converted to type float with CONVERT(float(53), 65) . UnitPrice needs one additional conversion with CONVERT(float(53) , (CONVERT(money, Quantity))) . This last conversion is to match data type float to float. The full conversion is then:

 CONVERT(float(53), (CONVERT(money, UnitPrice))) * CONVERT(float(53),  Quantity) > CONVERT(float(53), 65).

As far as date and time conversions go, they 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.