Existem métodos disponíveis para converter o valor do texto da string contido no atributo canonicalName do AD em um valor inteiro incrementado? Ou isso precisa ser realizado manualmente?

Por exemplo:

canonicalName (what I am getting)          hierarchyNode (what I need)
\domain.com\                               /1/
\domain.com\Corporate                      /1/1/
\domain.com\Corporate\Hr                   /1/1/1/
\domain.com\Corporate\Accounting           /1/1/2/
\domain.com\Users\                         /1/2/
\domain.com\Users\Sales                    /1/2/1/
\domain.com\Users\Whatever                 /1/2/2/
\domain.com\Security\                      /1/3/
\domain.com\Security\Servers               /1/3/1/
\domain.com\Security\Administrative        /1/3/2/
\domain.com\Security\Executive             /1/3/3/

Estou extraindo objetos de usuário em um banco de dados do SQL Server para fins de relatório. Os objetos do usuário estão espalhados por várias UOs da floresta. Portanto, identificando o nó mais alto da árvore que contém os usuários, posso utilizar o método GetDescendent () do SQL Server para recuperar rapidamente os usuários recursivamente sem precisar gravar1 + n número de sub-seleções.

Para referência:https://docs.microsoft.com/en-us/sql/t-sql/data-types/hierarchyid-data-type-method-reference

ATUALIZAR:

Eu sou capaz de converter o canonicalName de string para inteiro (veja abaixo usando o SQL Server 2014). No entanto, isso não parece resolver o meu problema. Eu construí apenas os galhos da árvore retirando as folhas, para que eu possa obter IsDescendant () pelo galho da árvore. Mas agora, não consigo inserir as folhas no lote, pois parece que preciso de GetDescendant (), que parece ter sido criado para manipular inserções uma por vez.

Como criar a árvore de diretórios do Active Directory, semelhante aos caminhos do sistema de arquivos, como uma Hierarquia SQL? Todos os exemplos tratam a hierarquia como um relacionamento imediatamente pai / filho e usam um CTE recursivo para construir a partir da raiz, o que exige que o relacionamento entre pais e filhos já seja conhecido. No meu caso, o relacionamento pai-filho é conhecido apenas através do delimitador '/'.

-- Drop and re-create temp table(s) that are used by this procedure.
IF OBJECT_ID(N'Tempdb.dbo.#TEMP_TreeHierarchy', N'U') IS NOT NULL 
BEGIN
DROP TABLE #TEMP_TreeHierarchy
END;

-- Drop and re-create temp table(s) that are used by this procedure.
IF OBJECT_ID(N'Tempdb.dbo.#TEMP_AdTreeHierarchyNodeNames', N'U') IS NOT NULL 
BEGIN
DROP TABLE #TEMP_AdTreeHierarchyNodeNames
END;

-- CREATE TEMP TABLE(s)
CREATE TABLE #TEMP_TreeHierarchy(
        TreeHierarchyKey INT IDENTITY(1,1) NOT NULL
        ,TreeHierarchyId hierarchyid NULL
        ,TreeHierarchyNodeLevel int NULL
        ,TreeHierarchyNode varchar(255) NULL
        ,TreeCanonicalName varchar(255) NOT NULL
    PRIMARY KEY CLUSTERED 
(
    TreeCanonicalName ASC
))

CREATE TABLE #TEMP_AdTreeHierarchyNodeNames (
        TreeCanonicalName VARCHAR(255) NOT NULL
        ,TreeHierarchyNodeLevel INT NOT NULL
        ,TreeHierarchyNodeName VARCHAR(255) NOT NULL
        ,IndexValueByLevel INT NULL
    PRIMARY KEY CLUSTERED 
(
    TreeCanonicalName ASC
    ,TreeHierarchyNodeLevel ASC
    ,TreeHierarchyNodeName ASC
))

-- Step 1.) INSERT the DISTINCT list of CanonicalName values into #TEMP_TreeHierarchy. 
--          Remove the reserved character '/' that has been escaped '\/'. Note: '/' is the delimiter.
--          Remove all of the leaves from the tree, leaving only the root and the branches/nodes.
    ;WITH CTE1 AS (SELECT CanonicalNameParseReserveChar = REPLACE(A.CanonicalName, '\/', '') -- Remove the reserved character '/' that has been escaped '\/'.
                   FROM dbo.AdObjects A
                  )

    -- Remove CN from end of string in order to get the distinct list (i.e., remove all of the leaves from the tree, leaving only the root and the branches/nodes).
    -- INSERT the records INTO #TEMP_TreeHierarchy
    INSERT INTO #TEMP_TreeHierarchy (TreeCanonicalName)
    SELECT DISTINCT 
        CanonicalNameTree = REVERSE(SUBSTRING(REVERSE(C1.CanonicalNameParseReserveChar), CHARINDEX('/', REVERSE(C1.CanonicalNameParseReserveChar), 0) + 1, LEN(C1.CanonicalNameParseReserveChar) - CHARINDEX('/', REVERSE(C1.CanonicalNameParseReserveChar), 0)))
    FROM CTE1 C1

-- Step 2.) Get NodeLevel and NodeName (i.e., key/value pair).
--      Get the nodes for each entry by splitting out the '/' delimiter, which provides both the NodeLevel and NodeName.
--      This table will be used as scratch to build the HierarchyNodeByLvl,
--          which is where the heavy lifting of converting the canonicalName value from string to integer occurs.
--      Note: integer is required for the node name - string values are not allowed. Thus this bridge must be build dynamically.
--      Achieve dynamic result by using CROSS APPLY to convert a single delimited row into 1 + n rows, based on the number of nodes.
    -- INSERT the key/value pair results INTO a temp table.
    -- Use ROW_NUMBER() to identify each NodeLevel, which is the key.
    -- Use the string contained between the delimiter, which is the value.
    -- Combined, these create a unique identifier that will be used to roll-up the HierarchyNodeByLevel, which is a RECURSIVE key/value pair of NodeLevel and IndexValueByLevel.
    -- The rolled-up value contained in HierarchyNodeByLevel is what the SQL Server hierarchyid::Parse() function requires in order to create the hierarchyid.
    -- https://blog.sqlauthority.com/2015/04/21/sql-server-split-comma-separated-list-without-using-a-function/
    INSERT INTO #TEMP_AdTreeHierarchyNodeNames (TreeCanonicalName, TreeHierarchyNodeLevel, TreeHierarchyNodeName)
    SELECT TreeCanonicalName
        ,TreeHierarchyNodeLevel = ROW_NUMBER() OVER(PARTITION BY TreeCanonicalName ORDER BY TreeCanonicalName)
        ,TreeHierarchyNodeName = LTRIM(RTRIM(m.n.value('.[1]','VARCHAR(MAX)')))
    FROM (SELECT TH.TreeCanonicalName
            ,x = CAST('<XMLRoot><RowData>' + REPLACE(TH.TreeCanonicalName,'/','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML)
          FROM #TEMP_TreeHierarchy TH
          ) SUB1
    CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)

-- Step 3.) Get the IndexValueByLevel RECURSIVE key/value pair
--        Get the DISTINCT list of TreeHierarchyNodeLevel, TreeHierarchyNodeName first
--        Use TreeHierarchyNodeLevel is the key
--        Use ROW_NUMBER() to identify each IndexValueByLevel, which is the value. 
--        Since the IndexValueByLevel exists for each level, the value for each level must be concatenated together to create the final value that is stored in TreeHierarchyNode
    ;WITH CTE1 AS (SELECT DISTINCT TreeHierarchyNodeLevel, TreeHierarchyNodeName
                    FROM #TEMP_AdTreeHierarchyNodeNames
                    ),
        CTE2 AS (SELECT C1.*
                        ,IndexValueByLevel = ROW_NUMBER() OVER(PARTITION BY C1.TreeHierarchyNodeLevel ORDER BY C1.TreeHierarchyNodeName)
                FROM CTE1 C1
                )

    UPDATE TMP1
    SET TMP1.IndexValueByLevel = C2.IndexValueByLevel
    FROM #TEMP_AdTreeHierarchyNodeNames TMP1
    INNER JOIN CTE2 C2
        ON TMP1.TreeHierarchyNodeLevel = C2.TreeHierarchyNodeLevel
        AND TMP1.TreeHierarchyNodeName = C2.TreeHierarchyNodeName

-- Step 4.) Build the TreeHierarchyNodeByLevel.
--          Use FOR XML to roll up all duplicate keys in order to concatenate their values into one string.
--          https://www.mssqltips.com/sqlservertip/2914/rolling-up-multiple-rows-into-a-single-row-and-column-for-sql-server-data/
    ;WITH CTE1 AS (SELECT DISTINCT TreeCanonicalName
                                ,TreeHierarchyNodeByLevel = 
                                   (SELECT '/' + CAST(IndexValueByLevel AS VARCHAR(10))
                                    FROM #TEMP_AdTreeHierarchyNodeNames TMP1
                                    WHERE TMP1.TreeCanonicalName = TMP2.TreeCanonicalName
                                    FOR XML PATH(''))
                    FROM #TEMP_AdTreeHierarchyNodeNames TMP2
                    ),
            CTE2 AS (SELECT C1.TreeCanonicalName
                            ,C1.TreeHierarchyNodeByLevel
                            ,TreeHierarchyNodeLevel = MAX(TMP1.TreeHierarchyNodeLevel)
                    FROM CTE1 C1
                    INNER JOIN #TEMP_AdTreeHierarchyNodeNames TMP1
                        ON TMP1.TreeCanonicalName = C1.TreeCanonicalName
                    GROUP BY C1.TreeCanonicalName, C1.TreeHierarchyNodeByLevel
                    )

    UPDATE TH
    SET TH.TreeHierarchyNodeLevel = C2.TreeHierarchyNodeLevel
        ,TH.TreeHierarchyNode = C2.TreeHierarchyNodeByLevel + '/'
        ,TH.TreeHierarchyId = hierarchyid::Parse(C2.TreeHierarchyNodeByLevel + '/')
    FROM #TEMP_TreeHierarchy TH
    INNER JOIN CTE2 C2
        ON TH.TreeCanonicalName = C2.TreeCanonicalName

INSERT INTO AD.TreeHierarchy (EffectiveStartDate, EffectiveEndDate, TreeCanonicalName, TreeHierarchyNodeLevel, TreeHierarchyNode, TreeHierarchyId)
SELECT EffectiveStartDate = CAST(GETDATE() AS DATE)
        ,EffectiveEndDate = '12/31/9999'
        ,TH.TreeCanonicalName
        ,TH.TreeHierarchyNodeLevel
        ,TH.TreeHierarchyNode
        ,TH.TreeHierarchyId 
FROM #TEMP_TreeHierarchy TH
ORDER BY TH.TreeHierarchyKey

---- For testing purposes only.
SELECT * FROM AD.TreeHierarchy TH
SELECT * FROM #TEMP_AdTreeHierarchyNodeNames
SELECT * FROM #TEMP_TreeHierarchy

-- Clean-up. DROP TEMP TABLE(s).
DROP TABLE #TEMP_TreeHierarchy
DROP TABLE #TEMP_AdTreeHierarchyNodeNames