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.


5 Responses to “XPath and XQuery Functions in SQL Server vs. DB2”

  1. Jim Smith Says:

    Hi. I am new to XML and would like to learn both XML and XQuery. Can you suggest books for self study purposes.



  2. Hi Jim,

    for starters there is quite some free material on the web. As just one example, http://www.w3schools.com offers a variety of introductory tutorials on various XML topics:


    And you’ll also find many other online tutorials.

    As for books, different people like different styles and types of books, so there isn’t a single best recommendation. You may want to check out the following popular XML books:

    “XML in a Nutshell”, 3rd Edition, by Elliotte Rusty Harold and Scott Means (O’Reilly,ISBN 0-596-00764-7)

    “Beginning XML”, 4th Edition, by David Hunter et al. (Wrox, ISBN 0-470-11487-8)

    “Professional XML”, by Bill Evjen et al. (Wrox, ISBN 0-471-77777-3)

    There are also books specifically on XPath and XQuery, such as “XQuery from the Experts: A Guide to the W3C XML Query Language“, written by Don Chamberlin and others.

    The DB2 pureXML Cookbook also contains detailed coverage of XPath, XQuery, SQL/XML, and XQuery Updates, with hundreds of examples that range from very basic to intermediate and advanced statements.

    Happy Reading!


  3. srini Says:


    The information which iam try to manage is dynamically changing as am still in developing process. I think using xml as the data column for this information would make it easy for me to expand the structure at any time during the process. I am using ASP.NET MVC3 and I have decided to use DB2 for xml data mangement instead of using MS sql server 2008, in the given situation, i can not have my manage objects loaded in server and executed.. as DB2 would not support CLR assembly to be loaded in it… am I right?

    Is there any alternate solution, which would allow me it use the managed assembly in the DB2 and execute the managed store procedures.

    Please suggest me ASAP.

    thank you,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: