May 21, 2009 at 9:01 am
We have a web service that delivers XML results that vary based on the caller. For example, caller A gets elements 1,2,3,4, and 5 while caller B gets elements 1,3, and 4. We store the XML containing every possible element in a table ahead of time and then transform the results at call time. The developer working on this project chose to implement a CLR function to take care of the transformations since there is no way to do XSLT in TSQL. The function takes two arguments - the source XML to be transformed and the XSL for the transformation - and returns the transformed XML.
Unfortunately this isn't scaling very well - I'm seeing a lot of CLR_CRST wait types under load. I am trying to track down the source code to see if there's anything egregious, but in the meantime I'm looking for some opinions. I know what mine is, but I'd like to hear what other people think about doing XSLT in SQL via a CLR. How would you do it (i.e. client side or in a CLR), and why?
May 21, 2009 at 9:18 am
I would venture a guess that the code came from, or is extremely similar to the following blog:
http://blogs.msdn.com/mrorke/archive/2005/06/28/433471.aspx
The problem with this is that it doesn't scale well inside of SQL Server. Is this a x86 or a x64 server? If it is x86 I am surprised you don't get OOM exceptions running the transformations.
XSLT is expensive memory wise. This kind of operation is best left to the application tier to perform, either before the data enters the database, or when the data is being pulled from the database. Database Servers are fairly expensive and are harder to replace or scale up than application servers. If load becomes a problem on application servers, adding another server to help handle the load is much cheaper than scaling up your SQL Server to handle the load.
Why exactly was this done in SQLCLR, aside from the fact that you can do it? Just because you can doesn't mean that you should.
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
May 21, 2009 at 9:19 am
Some more information: The source is essentially verbatim from this blog post: http://blogs.msdn.com/mrorke/archive/2005/06/28/433471.aspx
So aside from whether or not XSLT should or should not be done in SQL, is there a better way to do what this code is doing in a CLR?
May 21, 2009 at 10:22 am
You write the XML comes from a web-service. So there must be any application which takes the XML. Why don't use this application for the transformation?
However, if you have to handle this in SQL Server there are some things to optimize.
1.)
Don't load (and compile) the XSLT again and again. Use a static Dictionary which contains all loaded XSLTs. So every XSLT is loaded and compiled only once.
2.)
Do not load the XML into TSQL -> post to CLR -> get XML back to TSQL. I recommend to use a complete CLR solution. Just call the CLR procedure with the XML file name and the XSLT file name or id. Let .NET load the XML and just return the parsed data (not as XML if possible). This requires external access for your assembly.
May 28, 2009 at 7:16 am
I have had good responses do xslt via CLR as long as the payloads are not too large (300k+) depending on you resources you can expand here. This is because I could not find anyway to transform in .NET without consuming the entire payload.
You will find that doing xml imports on files larger than a few meg is much, much faster then native sql import options. by first transform to a flat file then firehose via BULK INSERT.
here's my xslt CLR routines (as is):
----------------------------------------------------------
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Xml;
using System.Xml.Xsl;
using System.Xml.XPath;
using System.IO;
using System.Text;
public partial class UserDefinedFunctions
{
///
///
/// xml to transform
/// file path to xslt
/// a name value collection of parameters in xml:
///
[Microsoft.SqlServer.Server.SqlFunction]
[return: SqlFacet(MaxSize=-1)]
public static SqlString udf_XSLT_XmlToString(SqlXml aSourceXml, SqlString aXSLPath, SqlXml aParamXML)
{
XslCompiledTransform xslt = new XslCompiledTransform();
using (MemoryStream ms = new MemoryStream())
{
using (StreamReader sr = new StreamReader(ms))
{
xslt.Load(aXSLPath.ToString());
xslt.Transform(aSourceXml.CreateReader(), getXsltArgumentList(aParamXML), ms);
string buff = string.Empty;
ms.Flush();
ms.Position = 0;
return new SqlString(sr.ReadToEnd());
}
}
}
///
///
/// file to source xml
/// file path to xslt
/// a name value collection of parameters in xml
/// transformation
[Microsoft.SqlServer.Server.SqlFunction]
[return: SqlFacet(MaxSize = -1)]
public static SqlBytes udf_XSLT_FileToBytes(string aXmlSourcePath, string aXSLPath, SqlXml aParamXML)
{
XslCompiledTransform xslt = new XslCompiledTransform();
xslt.Load(aXSLPath);
using (MemoryStream ms = new MemoryStream())
{
xslt.Transform(aXmlSourcePath, getXsltArgumentList(aParamXML),ms);
return new SqlBytes(ms);
}
}
///
///
/// file to source xml
/// file path to xslt
/// a name value collection of parameters in xml
/// target path
///
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlInt16 udf_XSLT_FileToFile(string aXmlSourcePath, string aXSLPath, SqlXml aParamXML, string aTargetPath)
{
XslCompiledTransform xslt = new XslCompiledTransform();
xslt.Load(aXSLPath);
using (FileStream stream = new FileStream(aTargetPath, FileMode.Create, FileAccess.Write, FileShare.Read))
{
xslt.Transform(aXmlSourcePath, getXsltArgumentList(aParamXML), stream);
return 1;
}
}
///
///
/// xml to transform
/// file path to xslt
/// a name value collection of parameters in xml:
/// format: <params><param name="paramName1">paramValue1</param><
/// param name="paramName2">paramValue2</param></params>
///
///
[Microsoft.SqlServer.Server.SqlFunction]
[return: SqlFacet(MaxSize = -1)]
public static SqlString udf_XSLT_XmlToUnicode(SqlXml aSourceXml, SqlString aXSLPath, SqlXml aParamXML)
{
XslCompiledTransform xslt = new XslCompiledTransform();
using (MemoryStream ms = new MemoryStream())
{
xslt.Load(aXSLPath.ToString());
ms.Position = 0;
xslt.Transform(aSourceXml.CreateReader(), getXsltArgumentList(aParamXML), ms);
return new SqlString(System.Globalization.CultureInfo.CurrentCulture.LCID,
SqlCompareOptions.IgnoreCase | SqlCompareOptions.IgnoreKanaType | SqlCompareOptions.IgnoreWidth
, ms.ToArray(), true);
}
}
///
///
/// xml to transform
/// file path to xslt
/// a name value collection of parameters in xml:
/// format: <params><param name="paramName1">paramValue1</param><
/// param name="paramName2">paramValue2</param></params>
///
/// target file
/// 1 if successful
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlInt16 udf_XSLT_XmlToFile(SqlXml aSourceXml, SqlString aXSLPath, SqlXml aParamXML, SqlString aDestFilePath)
{
XslCompiledTransform xslt = new XslCompiledTransform();
using (FileStream fstream = new FileStream((string)aDestFilePath, FileMode.Create))
{
xslt.Load(aXSLPath.ToString());
xslt.Transform(aSourceXml.CreateReader(), getXsltArgumentList(aParamXML), fstream);
fstream.Flush();
return 1;
}
}
private static XsltArgumentList getXsltArgumentList(SqlXml aParamXML)
{
XsltArgumentList args = new XsltArgumentList();
using (XmlReader reader = aParamXML.CreateReader())
{
while (reader.Read())
{
if (reader.Name == "param" && reader.IsStartElement())
{
string pname = reader.GetAttribute(0);
string pval = reader.ReadString();
args.AddParam(pname, "", pval);
}
}
}
return args;
}
///
///
/// xml to transform
/// file path to xslt
/// a name value collection of parameters in xml:
/// format: <params><param name="paramName1">paramValue1</param><
/// param name="paramName2">paramValue2</param></params>
///
/// XSLT results as XML
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlXml udf_XSLT_XmlToXml(SqlXml aSourceXml, SqlString aXSLPath, SqlXml aParamXML)
{
// Load XSL transformation
XslCompiledTransform xslt = new XslCompiledTransform();
xslt.Load((string)aXSLPath);
// Execute/Cache results
using (MemoryStream ms = new MemoryStream())
{
xslt.Transform(aSourceXml.CreateReader(), getXsltArgumentList(aParamXML), ms);
ms.Flush();
// Return results
ms.Position = 0;
return new SqlXml(ms);
}
}
}
December 24, 2009 at 2:11 pm
Any ideas as to the cause of the error below - from running the XML to XML function?
System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.ObjectDisposedException: Cannot access a disposed object.
Object name: 'Invalid attempt to call Read when the stream is closed.'.
System.ObjectDisposedException:
at System.Data.SqlTypes.SqlXmlStreamWrapper.ThrowIfStreamClosed(String method)
at System.Data.SqlTypes.SqlXmlStreamWrapper.Read(Byte[] buffer, Int32 offset, Int32 count)
at System.Xml.XmlReader.CreateSqlReader(Stream input, XmlReaderSettings settings, XmlParserContext inputContext)
System.Reflection.TargetInvocationException:
at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
at System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.Reflection.MethodBase.Invoke(Object obj, Object[] parameters)
at System.Data.SqlTypes.SqlXml.CreateReader()
at System.Data.SqlServer.Internal.XvarBlobStream.ReplaceWithSqlXml(SqlXml sxmlSource)
December 25, 2009 at 2:52 am
Hi Matthew
System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.ObjectDisposedException: Cannot access a disposed object.
Object name: 'Invalid attempt to call Read when the stream is closed.'.
System.ObjectDisposedException:
at System.Data.SqlTypes.SqlXmlStreamWrapper.ThrowIfStreamClosed(String method)
at System.Data.SqlTypes.SqlXmlStreamWrapper.Read(Byte[] buffer, Int32 offset, Int32 count)
at System.Xml.XmlReader.CreateSqlReader(Stream input, XmlReaderSettings settings, XmlParserContext inputContext)
System.Reflection.TargetInvocationException:
at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
at System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.Reflection.MethodBase.Invoke(Object obj, Object[] parameters)
at System.Data.SqlTypes.SqlXml.CreateReader()
at System.Data.SqlServer.Internal.XvarBlobStream.ReplaceWithSqlXml(SqlXml sxmlSource)
You are working with a SqlXml data type wrapper whose native (unmanaged) resources seem to be disposed. How do you get the SqlXml object? Do you store it in static member variables? Did you call "Dispose" on the stream or did you initialize a stream within a "using" block?
Greets
Flo
January 16, 2010 at 11:59 am
Sorry for taking so long to come back to this. The function is nearly identical to one posted above, except that the xslt xml is also passed in as a SqlXml parameter.
public static SqlXml XsltXml2Xml(SqlXml source, SqlXml transform, SqlXml transformParameters)
{
if (source.IsNull)
return SqlXml.Null;
if (transform.IsNull)
return source;
// load xslt
XslCompiledTransform xslt = new XslCompiledTransform();
xslt.Load(transform.CreateReader());
// execute xslt
using (MemoryStream ms = new MemoryStream())
{
xslt.Transform(source.CreateReader(), getXsltParameters(transformParameters), ms);
ms.Flush();
ms.Position = 0;
return new SqlXml(ms);
}
}
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply