SQL 2005 - CLR Integration - Custom XQuery/XPath functions (C#)

March 23, 2010 by C#   SQL  

Last month we started to make extensive use of XML Types in MS SQL 2005 in one of our projects. One of the requirements were that an user must be able to search within specific nodes.

So I thought, awesome we'll make use of XQueries/XPath's within SQL to query these XML fields - everything went well - until I wanted to write a case insensitive (upper/lower case functions) search query and found that SQL doesn't fully support XQuery/XPath

Infact, Microsoft only recently added the functions I need to SQL 2008.

a Quick search on google revealed that a lot of people were suggesting that one should use the translate function (as workaround) available in SQL 2005's XQuery.

I do however feel that this is one of those things we can solve using CLR Integration - which would also make it possible to add any other functionality we might need as well.

In my example, we're going to create an upper-case function for XML Types within SQL 2005.

You will need to create a context class inheriting from the abstract class XsltContext(System.Xml.Xsl)

public class CustomContext : XsltContext
{
	public override IXsltContextFunction ResolveFunction(string prefix, string name, XPathResultType[] ArgTypes)
	{
		if (name == "upper-case")
		{
			return new UpperCase();
		}
		return null;
	}
...
...

We also need to create a class inheriting from the IXsltContextFunction Interface (which contains the implementation of our function), which would basically look like this:
public class UpperCase : IXsltContextFunction
{
	public UpperCase()
	{
	}

	public XPathResultType[] ArgTypes
	{
		get { return null; }
	}

	public object Invoke(XsltContext xsltContext, object[] args, XPathNavigator docContext)
	{
		return args[0].ToString().ToUpper();
	}

	public int Maxargs
	{
		get { return 1; }
	}

	public int Minargs
	{
		get { return 1; }
	}

	public XPathResultType ReturnType
	{
		get { return XPathResultType.String; }
	}
}

To make this work (e.g. wire everything up), we need to add our "context class" to our XPathExpression like this - which makes our newly created function accessible:
XPathDocument doc = new XPathDocument(@"items.xml");
XPathNavigator nav = doc.CreateNavigator();
CustomContext ctx = new CustomContext();

XPathExpression expr = nav.Compile(@"*//item[contains(upper-case(string(@value)), 'AB')]");
expr.SetContext(ctx);
XPathNodeIterator nodes = nav.Select(expr);
foreach (XPathItem node in nodes)
{
	Console.WriteLine(node.Value);
}

Finally we add this functionality to our CLR Function like so:
[SqlFunction]
public static bool XQuery([SqlFacet(MaxSize = -1)]String value, String expression)
{
	if (!String.IsNullOrEmpty(value))
	{
		using (StringReader sr = new StringReader(value))
		{
			XPathDocument doc = new XPathDocument(sr);
			XPathNavigator nav = doc.CreateNavigator();
			CustomContext ctx = new CustomContext();
			XPathExpression expr = nav.Compile(expression);
			expr.SetContext(ctx);
			XPathNodeIterator nodes = nav.Select(expr);
			return nodes.Count > 0;
		}
	}
	return false;
}

Register the assembly:
CREATE ASSEMBLY CLRDemos FROM 'C:\procs\CLRXQuery.dll' WITH PERMISSION_SET = SAFE;

Reference the function:
CREATE FUNCTION XQuery
(
	@value NVARCHAR(MAX),
	@expression NVARCHAR(4000)
) 
RETURNS BIT
AS 
EXTERNAL NAME [CLRDemos].[CSTruter.com.functions].[XQuery]

Example of usage:
SELECT * FROM friends 
WHERE dbo.XQuery(CAST(details AS VARCHAR(MAX)), '//item[upper-case(string(@value))="DEF"]') = 1

Like you can see its quite simple adding non-supported features.


Leave a Comment




Related Downloads

Add missing XQuery functions to SQL using C# CLR Procedure

Simple C# SQL CLR Stored Procedure and Function Demo