XPath and XQuery Functions in SQL Server vs. DB2
January 18, 2010
XQuery is a feature-rich language that allows you to code sophisticated queries against XML data. As you probably know, the XPath language is a subset of XQuery. XPath and XQuery share a common set of built-in functions that are defined by the W3C specification titled “XQuery 1.0 and XPath 2.0 Functions and Operators“. This spec describes dozens and dozens of functions that deal with strings, numbers, dates, timestamps, XML nodes and sequences, and so on.
The DB2 support for the XPath and XQuery functions is documented in the DB2 Information Center, and you might find corresponding information for other database systems on their respective web sites. However, other databases might not support as many of the XQuery functions as DB2. For example, DB2 9.7 for Linux, UNIX, and Windows supports 21 XQuery string functions while SQL Server 2008 seems to have just six. DB2 also supports over 30 XQuery date and time functions, SQL Server seems to support none of those.
Admittedly, some functions are more commonly used then others. XQuery functions such as “substring” or “count” are used quite often. In contrast, the XQuery functions “reverse” and “string-to-codepoints” tend to be used much less frequently.
But then there are some functions that you might not use every day, but when you do need them you really don’t want to miss them. For example, sometimes you may have to
• search for strings that start with a given prefix (starts-with)
• determine whether a string matches a given pattern (matches)
• remove unnecessary spaces from a string (normalize-space)
• compute the absolute value of a number (abs)
• convert a timestamp to a different time zone (adjust-dateTime-to-timezone)
• decide whether two XML documents are identical in all their structure and values (deep-equal)
These and other tasks are common enough. Yes, you can write application code to perform such tasks, but that is much less convenient and less efficient than using a built-in function that’s readily available. Being aware of the available built-in functions can make your life much easier when you develop XML applications. The functions listed above are available in DB2 pureXML, but these and many others don’t seem to be supported in SQL Server 2008.