March 23, 2010 by Christoff Truter 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; } ... ...
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; } } }
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); }
[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; }
CREATE ASSEMBLY CLRDemos FROM 'C:\procs\CLRXQuery.dll' WITH PERMISSION_SET = SAFE;
CREATE FUNCTION XQuery ( @value NVARCHAR(MAX), @expression NVARCHAR(4000) ) RETURNS BIT AS EXTERNAL NAME [CLRDemos].[CSTruter.com.functions].[XQuery]
SELECT * FROM friends WHERE dbo.XQuery(CAST(details AS VARCHAR(MAX)), '//item[upper-case(string(@value))="DEF"]') = 1