Archive

Posts Tagged ‘SQL Server’

Materiales del CodeCamp BA 2011 – Datos en la nube (Azure) y Business Intelligence (PowerPivot)

October 21, 2011 adglopez and LeandroDG No comments

Hola a todos, les dejamos disponible los materiales de las charlas del codecamp que estuvimos dando Leandro y yo (Adrian)

- Haciendo BI rápido y flexible
- Datos en la nube: colas, caché, topics y otras yerbas

Descarga de los materiales:
- Haciendo BI rápido y flexible (202)
- Datos en la nube: colas, caché, topics y otras yerbas (330)

VN:F [1.9.11_1134]
Rating: 5.0/5 (2 votes cast)

StreamInsight resources in MSDN and CodePlex

September 10, 2010 adglopez No comments

Hi there, today a new version of StreamInsight documentation in MSDN was deployed.

You’ll find useful and updated documentation and also you can download updated samples from codeplex, that work with the current release.

Take a look and enjoy! :)

VN:F [1.9.11_1134]
Rating: 0.0/5 (0 votes cast)

Dynamic XPath function with SqlClr in SQL Server

November 14, 2009 adglopez No comments

In my previous post I commented an approach to perform queries with dynamic XPath. It was useful initially to determine the result in a stand alone sql sentence, for example:

exec sp_XPath @xml, @xpath, @result output

But if we need to use it for example in a where clause we’ll need a function. Since the previous approach uses sp_executesql it doesn’t suit well in SQL Server.

So I decided to use SqlClr to create the function:

To create the assembly just add a new SqlClr C# Project, add a user defined function and paste this code:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Xml;
using System.Xml.XPath;
using System.IO;
using System.Text;

public partial class UserDefinedFunctions
{
	[Microsoft.SqlServer.Server.SqlFunction]
	public static SqlString XPath(SqlXml message, string xpath)
	{
		if (message.IsNull || string.IsNullOrEmpty(xpath))
			return string.Empty;

		XPathDocument doc = new XPathDocument(new StringReader(message.Value));
		XPathNavigator nav = doc.CreateNavigator();
		XPathExpression expr = nav.Compile(xpath);
		string result = string.Empty;

		switch (expr.ReturnType)
		{
			case XPathResultType.Boolean:
			case XPathResultType.Number:
			case XPathResultType.String:
				result = nav.Evaluate(expr).ToString();
				break;

			case XPathResultType.NodeSet:
				XPathNodeIterator iterator = (XPathNodeIterator)nav.Evaluate(expr);
				XmlDocument xmldoc = new XmlDocument();
				xmldoc.LoadXml(message.Value);
				XmlNodeList nodes = xmldoc.SelectNodes(xpath);
				foreach (XmlNode node in nodes)
					result += node.OuterXml;
				break;

			case XPathResultType.Error:
				throw new ArgumentException("Invalid XPath query.");
		}
		return new SqlString(result);
	}
}

Hope this helps :)

VN:F [1.9.11_1134]
Rating: 0.0/5 (0 votes cast)
Categories: Development Tags: , , , ,