Archive

Archive for November, 2009

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: , , , ,

Dynamic XPath using Xml DataType in SQL Server

November 13, 2009 adglopez No comments

Hi, today I was working within SQL trying to execute a query against a variable of Xml type. Something like this:

DECLARE
	@xml xml,
	@xpath varchar(512)

SET @xml = '<Hello>world</Hello>'
SELECT @xml.value(@xpath,'varchar(255)')

The error trying to run this was:

The argument 1 of the XML data type method "value" must be a string literal.

Taking a look about how to solve this I found this thread on MSDN:
http://social.msdn.microsoft.com/Forums/en-US/sqlxml/thread/b93d0790-e239-459e-a4e3-7511475f548b

Continuing with the idea provided by Christian, I created a stored procedure that calls to sp_executesql using output parameters and wrapped the logic in a new stored procedure.

So basically, now you can run something like this:

EXECUTE [MyDB].[dbo].[Xpath] @xml,@xpath, @result output

Here is the code to create the stored procedure:

CREATE PROCEDURE Xpath (
	@xml xml,
	@xpath varchar(1024),
	@result varchar(255) OUTPUT
) AS
BEGIN
	DECLARE
		@EncodedXPath nvarchar(200),
		@Sql nvarchar(max),
		@SeqValue varchar(512),
		@paramDefinition nvarchar(255)

	SET @EncodedXPath = REPLACE(@xpath, '''', '''''')
	SET @Sql = N'SET @SeqValueOUT = @xmlVar.value(''' + @EncodedXPath + N''', ''varchar(25)'')'
	SET @paramDefinition  = '@xmlVar xml, @SeqValueOUT varchar(25) OUTPUT'

	EXEC sp_executesql @Sql, @paramDefinition, @xmlVar = @xml, @SeqValueOUT = @SeqValue OUTPUT

	SELECT @result = @SeqValue
END
GO

And, here is a sample on how to use it:

DECLARE
	@xml xml,
	@xpath nvarchar(200)

SET @xml='<Root><Device><Inspection><Status>OK</Status></Inspection></Device></Root>'
SET @xpath = '/Root[1]/Device[1]/Inspection[1]/Status[1]'

DECLARE @result varchar(255)
EXECUTE [MyDb].[dbo].[Xpath] @xml,@xpath, @result output

SELECT @result

Thanks again to Christian, the idea is based on his post.

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

Service Configuration Editor with 64-Bit OS error: An extension of name ‘persistenceProvider’ already appears…

November 3, 2009 LeandroDG No comments

I’m working on developing some WCF-Custom adapters for Biztalk 2009, so I needed to add some binding configuration to machine.config.

If you are trying to modify the computer WCF Configuration with the Service Configuration Editor to add a binding extension, behavior, binding element extension, etc., and you are working in a 64 bit environment, this error may occur:

An extension of name ‘persistenceProvider’ already appears in extension collection. Extension names must be unique. (C:WINDOWSMicrosoft.NETFrameworkv2.0.50727CONFIGmachine.config (line 224)

If you open the configuration file (C:WINDOWSMicrosoft.NETFrameworkv2.0.50727CONFIGmachine.config), you’ll probably find that it’s not repeated in that file. But it actually appears in another machine.config file.

If you check C:WINDOWSMicrosoft.NETFramework64v2.0.50727CONFIGmachine.config, it will probably contain the following:

<system.serviceModel>
 <extensions>
 <behaviorExtensions>
 <add name="persistenceProvider" type="System.ServiceModel.Configuration.PersistenceProviderElement, System.WorkflowServices, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
 <add name="workflowRuntime" type="System.ServiceModel.Configuration.WorkflowRuntimeElement, System.WorkflowServices, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
 <add name="enableWebScript" type="System.ServiceModel.Configuration.WebScriptEnablingElement, System.ServiceModel.Web, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
 <add name="webHttp" type="System.ServiceModel.Configuration.WebHttpElement, System.ServiceModel.Web, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
 <add name="Microsoft.VisualStudio.Diagnostics.ServiceModelSink.Behavior"
 type="Microsoft.VisualStudio.Diagnostics.ServiceModelSink.Behavior, Microsoft.VisualStudio.Diagnostics.ServiceModelSink, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
 </behaviorExtensions>
 <bindingElementExtensions>
 <add name="webMessageEncoding" type="System.ServiceModel.Configuration.WebMessageEncodingElement, System.ServiceModel.Web, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
 ...

Notice the Framework64 part in the path, this configuration is specific for 64-bit .NET applications.

So, to be able to correctly modify your machine.config using the Service Configuration Editor, you’ll need to temporarily comment the whole <system.servicemodel> in the <em>C:WINDOWSMicrosoft.NETFramework64v2.0.50727CONFIGmachine.config</em>  and save the file. Then use the Service Configuration Editor with your machine.config, edit and save. Finally, open the Framework64 machine.config again, uncomment the section, and save again.

Be very careful when modifying the machine.config files, it contains configuration that can affect every .NET application that you run in your PC.

VN:F [1.9.11_1134]
Rating: 5.0/5 (1 vote cast)