Use CLR function for convert Hierarchyid to string in visual studio 2019

Question

Please advise me. I try to write a CLR function for SQL server. I using the Microsoft document for write below function Ref-LINK Finding Ancestors by Using the CLR A common operation involving two nodes in a hierarchy is to find the lowest common ancestor. This can be written in either Transact-SQL or CLR, because the hierarchyid type is available in both. CLR is recommended because performance will be faster.

Use the following CLR code to list ancestors and to find the lowest common ancestor:

using Microsoft.SqlServer.Server;
using Microsoft.SqlServer.Types;
using System;
using System.Collections;
using System.Text;

public partial class HierarchyId_Operations
{
    [SqlFunction(FillRowMethodName = "FillRow_ListAncestors")]
    public static IEnumerable ListAncestors(SqlHierarchyId h)
    {
        while (!h.IsNull)
        {
            yield return (h);
            h = h.GetAncestor(1);
        }
    }
    public static void FillRow_ListAncestors(
        Object obj,
        out SqlHierarchyId ancestor
        )
    {
        ancestor = (SqlHierarchyId)obj;
    }

    public static HierarchyId CommonAncestor(
        SqlHierarchyId h1,
        HierarchyId h2
        )
    {
        while (!h1.IsDescendantOf(h2))
            h1 = h1.GetAncestor(1);

        return h1;
    }
}

But i see this error :

I try to use Visual Studio solution's, but not work truly . then use internet search and find Nugget , but my nugget is disable.

Answer 1

The name HierarchyId in the .NET code appears to be a type-o in the documentation. There is only SqlHierarchyId within SQLCLR. In fact, this was even mentioned in one of the 3 closed issues associated with that documentation page (at the bottom of that page, click on the "Closed" tab):

CommonAncestor correctness

Opened by "FYfrog" on 2019-06-27.