Archive
Dynamic XPath function with SqlClr in SQL Server
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 
Dynamic XPath using Xml DataType in SQL Server
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.
Service Configuration Editor with 64-Bit OS error: An extension of name ‘persistenceProvider’ already appears…
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.