The query I was having problems with was something like this (Thanks for the sample data wBob!):
DECLARE @xml XML; -- Spin up some test data WITH cte AS ( SELECT 65 x UNION ALL SELECT x + 1 FROM cte WHERE x < 90 ) SELECT @xml = (
SELECT CHAR( a.x ) + ' & co.' AS "name"
a.x AS "value"
'FIELD' AS "paramdata/field" FROM cte a CROSS JOIN cte b CROSS JOIN cte c
FOR XML PATH('root'), TYPE
) DECLARE @ConfigData TABLE (ID INT, Name NVARCHAR(255), Value NVARCHAR(255), ParamData XML)INSERT INTO @ConfigData (ID, Name , Value, ParamData) SELECT 1,
tbl.cols.value('name[1]', 'varchar(1000)'),
tbl.cols.value('value[1]', 'varchar(1000)'),
tbl.cols.query('./paramdata[1]')FROM @XML.nodes('//root') AS tbl(cols) GO
I raised this with MS via Connect (#562092) and went on to implement my own workaround using the following CLR shredding function:
public partial class UserDefinedFunctions
{
[SqlFunction(FillRowMethodName = "tvf_clr_FillParameterData",
TableDefinition = "Name nvarchar(255), Value nvarchar(255), ParamData nvarchar(255)")]
public static IEnumerable tvf_clr_ParameterDataShredder(SqlXml parameterData)
{
XmlDocument document = new XmlDocument();
document.LoadXml(parameterData.Value);
return document.SelectNodes("//parameter");
}
public static void tvf_clr_FillParameterData(object row, out string outName, out string outValue, out string outParamData)
{
XmlNode document = (XmlNode)row;
outName = document.SelectSingleNode("name").InnerXml;
outValue = document.SelectSingleNode("value").InnerXml;
outParamData = null;
if (document.SelectSingleNode("paramdata") != null)
outParamData = document.SelectSingleNode("paramdata").OuterXml;
}
};
This served me well and solved the performance issue and all was fine. Until now.
The thing is, this method doesn't handle decoding of escape characters in XML, so any &s that exist in the XML string being shredded will be returned as &:amp. Not ideal. My first thought was to just implement a decoding function within the CLR routine but the main candidates use System.Web which is not available within SQL Server. I eventually stumbled upon this post from Jonathan Keyahias which provided a sql safe Encoding implementation and I was going to use this as a basis for writing my own decoding function. While waiting the 20mins for Visual Studio 2008 to open, it occured to me to revisit the Connect case and fortunately, there were a number of workarounds there, and a combination of the solutions the most effective.
INSERT INTO @ConfigData (ID, Name , Value, ParamData)SELECT 1,
tbl.cols.value('(name/text())[1]', 'nvarchar(255)'),
tbl.cols.value('(value/text())[1]', 'nvarchar(255)'),
tbl.cols.query('./paramdata[1]') FROM @xml.nodes('//root') AS tbl(cols) OPTION ( OPTIMIZE FOR ( @xml = NULL ) )The above simple modification to my SQL INSERT statement and performance was acceptable and more importantly, the decoding of the escaped characters was fine. Saved me a lot of work.
