The biggest reason to move away from XslCompiledTransform
is that it is merely an XSLT 1.0 processor.
The majority of the functionality of XSLT 2.0 and XQuery 1.0 overlaps, and for the most part they are similar languages with different syntax (a little like C# and VB).
XSLT is a lot more verbose, but its templating features add a lot of functionality that can be fairly cumbersome to replicate in XQuery, particularly making small changes to node trees. The main features that are particularly cumbersome in XQuery are things like <xsl:template match="..." />
and <xsl:copy>...</xsl:copy>
.
XQuery has a much cleaner syntax (IMHO) as long as the templating features are not needed, and I find it is a lot better for more advanced computations, and retrieving data from large documents.
XQuery is often viewed as a database language. Whilst a lot of databases use it this way, it is not the only use for it. Some implementations of the language in databases are very restricted. Another commentor claims that XQuery is "very strongly typed". Unless you are using the static typing feature, XQuery is no more strongly typed than XSLT. Whilst some database implementations force you to use the static typing features, most other implementations are moving away from this.
He also claims that XQuery is not very good for "larger, more complicated things". I would have argued exactly the opposite. The conciseness and flavour of the syntax makes it far easier to write complicated functions and computations in XQuery. I have written a raytracer in XQuery, which feels really quite natural; I think it would be a lot harder (certainly more verbose) to write something this computationally complex in XSLT.
In summary:
XSLT is more natural for transformation. It is better if you have a document with roughly the right structure and you want to transform the components, for example rendering an HTML version of an XML file.
XQuery is more natural for building a new XML document from various sources, or for changing the structure of a document.
Note that the overlap is rather large and there is often no "right" choice, but if you make the wrong choice then you to tend to find yourself working against the grain.