Playing With SQL Server CLR Integration – Part II

Thursday, June 11, 2009

On my last post, I showed how to convert a property bag stored in text to a CLR Table-Valued Function.

I started thinking that I could retrieve the property values, but I couldn’t change them or add new properties.

Passing a table as a parameter is still not possible in SQL Server 2005. And that would force me to load the table into a variable, change it and load it back into the property bag.

Or I could create functions to Create, Update and Delete properties from the property bag.

Or I could just use XML. All it takes is creating a CLR Scalar-Valued Function to convert the property bag to an XML representation and another to convert the XML back to the property bag format.

It still makes me load the property bag into a variable if I want to change it, but it’s usable in SQL Server 2005, which was the targeted platform.

Converting the property bag to an XML document is easy using the previously created enumerator:

[Microsoft.SqlServer.Server.SqlFunction(
    Name = "ShortPropsToXml",
    IsDeterministic = true,
    IsPrecise = false,
    DataAccess = DataAccessKind.None,
    SystemDataAccess = SystemDataAccessKind.None)]
public static SqlXml ShortPropsToXml(string shortPropsText)
{
    var xml = new XElement("ShortProps",
        from shortProp in ShortPropsEnumerable(shortPropsText)
        select new XElement("p",
            new XAttribute("n", shortProp.Key),
            new XCData(shortProp.Value)));
<span style="color: blue">using </span>(<span style="color: blue">var </span>buffer = <span style="color: blue">new </span><span style="color: #2b91af">MemoryStream</span>())
{
    <span style="color: blue">using </span>(<span style="color: blue">var </span>xmlWriter = <span style="color: #2b91af">XmlWriter</span>.Create(buffer, <span style="color: blue">new </span><span style="color: #2b91af">XmlWriterSettings </span>{ CheckCharacters = <span style="color: blue">false </span>}))
    {
        xml.WriteTo(xmlWriter);
    }

    buffer.Position = 0;

    <span style="color: blue">using </span>(<span style="color: #2b91af">XmlReader </span>xmlReader = <span style="color: #2b91af">XmlReader</span>.Create(buffer, <span style="color: blue">new </span><span style="color: #2b91af">XmlReaderSettings </span>{ CheckCharacters = <span style="color: blue">false </span>}))
    {
        <span style="color: blue">return new </span><span style="color: #2b91af">SqlXml</span>(xmlReader);
    }
}

}

Converting the an XML document back into the property bag format is also easy:

[Microsoft.SqlServer.Server.SqlFunction(
    Name = "XmlToShortProps",
    IsDeterministic = true,
    IsPrecise = false,
    DataAccess = DataAccessKind.None,
    SystemDataAccess = SystemDataAccessKind.None)]
public static SqlChars XmlToShortProps(SqlXml shortPropsXml)
{
    var xml = XDocument.Parse(shortPropsXml.Value);

    var textBuilder = new StringBuilder();
    foreach (var item in xml.Document.Element("ShortProps").Elements("p"))
    {
        textBuilder.AppendFormat("[[[{1}]]]{0}{2}{0}", Environment.NewLine, item.Attribute("n").Value, item.Value);
    }

    return new SqlChars(textBuilder.ToString().ToCharArray());
}

Now it’s just uploading the new version of the assembly to the database:

ALTER ASSEMBLY [MyAssembly]
FROM '...\MyAssembly.dll'
GO

And defining the Scalar-Valued Functions in Transact-SQL:

CREATE FUNCTION [dbo].[ShortPropsToXml](@shortPropsText [nvarchar](4000))
RETURNS [xml] WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [Esi.SA.Encyclopedia].[ShortProps].[ShortPropsToXml]
GO

CREATE FUNCTION [dbo].[XmlToShortProps](@shortPropsXml [xml])
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [Esi.SA.Encyclopedia].[ShortProps].[XmlToShortProps]
GO

And it’s all set to go.

Now, given this property bag definition:

declare @text nvarchar(max)='[[[name1]]]
value1
[[[name2]]]
value2
[[[name3]]]
value3
'

I can change it into XML:

DECLARE @xml [xml] = dbo.ShortPropsToXml(@text)

Update:

<pre class="code"><span style="color: blue">set </span>@xml<span style="color: gray">.</span><span style="color: blue">modify</span><span style="color: gray">(</span><span style="color: red">'replace value of (/ShortProps/p[@n=&quot;name2&quot;]/text())[1] with &quot;new value2&quot;'</span><span style="color: gray">)

Insert:

<pre class="code"><span style="color: blue">set </span>@xml<span style="color: gray">.</span><span style="color: blue">modify</span><span style="color: gray">(</span><span style="color: red">'insert &lt;p n=&quot;name4&quot;&gt;Value4.1

Value4.2</p> after (/ShortProps/p[@n="name2"])[1]')

Delete

<pre class="code"><span style="color: blue">set </span>@xml<span style="color: gray">.</span><span style="color: blue">modify</span><span style="color: gray">(</span><span style="color: red">'delete (/ShortProps/p[@n=&quot;name3&quot;])[1]'</span><span style="color: gray">)</span></pre>

Convert back to the property bag format:

<pre class="code"><span style="color: blue">print </span>dbo<span style="color: gray">.</span>XmlToShortProps<span style="color: gray">(</span>@xml<span style="color: gray">)</span></pre>
<a href="http://11011.net/software/vspaste"></a>

<pre class="code">[[[name1]]]

value1 [[[name2]]] new value2 [[[name4]]] Value4.1 Value4.2

Query as a table:

<pre class="code"><span style="color: blue">select </span>T<span style="color: gray">.</span>C<span style="color: gray">.</span>value<span style="color: gray">(</span><span style="color: red">'./@n'</span><span style="color: gray">, </span><span style="color: red">'nvarchar(max)'</span><span style="color: gray">) </span><span style="color: blue">as </span>Name<span style="color: gray">, </span>T<span style="color: gray">.</span>C<span style="color: gray">.</span>value<span style="color: gray">(</span><span style="color: red">'.'</span><span style="color: gray">, </span><span style="color: red">'nvarchar(max)'</span><span style="color: gray">) </span><span style="color: blue">as </span>Value <span style="color: blue">from </span>@xml<span style="color: gray">.</span>nodes<span style="color: gray">(</span><span style="color: red">'/ShortProps/p'</span><span style="color: gray">) </span>T<span style="color: gray">(</span>C<span style="color: gray">)</span></pre>

<table style="border-bottom: black thin solid; border-left: black thin solid; border-top: black thin solid; border-right: black thin solid"><tbody>
    <tr>
      <th style="border-bottom: black thin solid; border-left: black thin solid; border-top: black thin solid; border-right: black thin solid">Name</th>

      <th style="border-bottom: black thin solid; border-left: black thin solid; border-top: black thin solid; border-right: black thin solid">Value</th>
    </tr>

    <tr>
      <td style="border-bottom: black thin solid; border-left: black thin solid; border-top: black thin solid; border-right: black thin solid">name1</td>

      <td style="border-bottom: black thin solid; border-left: black thin solid; border-top: black thin solid; border-right: black thin solid">value1</td>
    </tr>

    <tr>
      <td style="border-bottom: black thin solid; border-left: black thin solid; border-top: black thin solid; border-right: black thin solid">name2</td>

      <td style="border-bottom: black thin solid; border-left: black thin solid; border-top: black thin solid; border-right: black thin solid">new value2</td>
    </tr>

    <tr>
      <td style="border-bottom: black thin solid; border-left: black thin solid; border-top: black thin solid; border-right: black thin solid">name4</td>

      <td style="border-bottom: black thin solid; border-left: black thin solid; border-top: black thin solid; border-right: black thin solid">Value4.1 
        <br />Value4.2</td>
    </tr>
  </tbody></table>

Unfortunately, the application uses characters that are invalid to SQL Server as XML characters and I can’t use it on that application. I’ll have to understand those values better and add other fields to the table and XML.

No Comments