我希望我正确地做到了：

您从type =“ start”开始，然后走下层次结构，其中out -data是下一个节点的ID。该行的深度未定义，应在type =“ user”的节点处结束。

您的第二个示例具有2个任务，其类型为type =“ user”，但其中只有一个被引用为链中较高节点中的出数据。

我的示例将使用额外的EXISTS子句过滤第二个。

第一个CTE DerivedTable包含一个查询，您也可以使用隔离查询。它将以表格形式显示完整的信息。

第二个CTE是递归的，从起点开始并向下遍历整个链。等级列是链条的顺序。

由于您似乎只对最后一项感兴趣，因此第三CTE添加了反向排名。你可能会得到这个WHERE RevRank=1

DECLARE @process TABLE(ID INT IDENTITY, workflowXML XML);
INSERT INTO @process(workflowXML) VALUES
('<process>
      <Event type="start" id="StartEvent_1">
         <outgoing>SequenceFlow_0h5l5vu</outgoing>
      </Event>
      <Flow type="sequence"
            id="SequenceFlow_0h5l5vu"
            sourceRef="StartEvent_1"
            targetRef="Task_1qc93ha"/>
      <Flow type="sequence"
            id="SequenceFlow_120gi3p"
            sourceRef="Task_1qc93ha"
            targetRef="Task_0x1pjee"/>
      <Task type="service" id="Task_1qc93ha">
         <incoming>SequenceFlow_0h5l5vu</incoming>
         <outgoing>SequenceFlow_120gi3p</outgoing>
      </Task>
      <Task type="user" id="Task_0x1pjee">
         <incoming>SequenceFlow_120gi3p</incoming>
      </Task>
</process>')
,('<process id="Process_1" isExecutable="false">
      <Event type="start" id="StartEvent_142xowk">
         <outgoing>SequenceFlow_03yocm5</outgoing>
      </Event>
      <Flow type="sequence"
            id="SequenceFlow_03yocm5"
            sourceRef="StartEvent_142xowk"
            targetRef="Task_12g1q69"/>
      <Task type="user" id="Task_0x1pjee">
             <incoming>SequenceFlow_120gi3p</incoming>
       </Task>
      <Task type="user" id="Task_12g1q69">
         <incoming>SequenceFlow_03yocm5</incoming>
      </Task>
</process>');

-这是查询：

WITH DerivedTable AS
(
    SELECT prTbl.ID AS tblID
          ,nd.value('local-name(.)','nvarchar(max)') AS [Name]
          ,nd.value('@type','nvarchar(max)') AS [Type]
          ,nd.value('@id','nvarchar(max)') AS Id
          ,COALESCE(nd.value('@sourceRef','nvarchar(max)')
                   ,nd.value('(incoming)[1]','nvarchar(max)')) AS [In]
          ,COALESCE(nd.value('@targetRef','nvarchar(max)')
                   ,nd.value('(outgoing)[1]','nvarchar(max)')) AS [Out]
    FROM @process AS prTbl
    CROSS APPLY prTbl.workflowXML.nodes('process') AS A(pr)
    CROSS APPLY pr.nodes('*') AS B(nd)
)
,recCTE AS
(
    SELECT tblID,[Name],[Type],Id,[In],[Out],1 AS [Rank]
    FROM DerivedTable 
    WHERE [Type]='start'

    UNION ALL

    SELECT x.tblID,x.[Name],x.[Type],x.Id,x.[In],x.[Out],r.[Rank]+1
    FROM recCTE AS r
    INNER JOIN DerivedTable AS x ON x.[Id]=r.[Out] 
                                    AND EXISTS(SELECT 1 
                                               FROM DerivedTable AS y 
                                               WHERE y.tblID=x.tblID AND y.[Out]=x.[Id])
)
,ReverseRank AS
(
    SELECT *
          ,ROW_NUMBER() OVER(PARTITION BY tblID ORDER BY [Rank] DESC) AS RevRank 
    FROM recCTE
)
SELECT * 
FROM ReverseRank
ORDER BY  tblID,[Rank]

结果（您的预期输出为RevRank = 1）：

+-------+------+---------+-------+----------+----------------------+----------------------+----------------------+
| tblID | Rank | RevRank | Name  | Type     | Id                   | In                   | Out                  |
+-------+------+---------+-------+----------+----------------------+----------------------+----------------------+
| 1     | 1    | 5       | Event | start    | StartEvent_1         | NULL                 | SequenceFlow_0h5l5vu |
+-------+------+---------+-------+----------+----------------------+----------------------+----------------------+
| 1     | 2    | 4       | Flow  | sequence | SequenceFlow_0h5l5vu | StartEvent_1         | Task_1qc93ha         |
+-------+------+---------+-------+----------+----------------------+----------------------+----------------------+
| 1     | 3    | 3       | Task  | service  | Task_1qc93ha         | SequenceFlow_0h5l5vu | SequenceFlow_120gi3p |
+-------+------+---------+-------+----------+----------------------+----------------------+----------------------+
| 1     | 4    | 2       | Flow  | sequence | SequenceFlow_120gi3p | Task_1qc93ha         | Task_0x1pjee         |
+-------+------+---------+-------+----------+----------------------+----------------------+----------------------+
| 1     | 5    | 1       | Task  | user     | Task_0x1pjee         | SequenceFlow_120gi3p | NULL                 |
+-------+------+---------+-------+----------+----------------------+----------------------+----------------------+
| 2     | 1    | 3       | Event | start    | StartEvent_142xowk   | NULL                 | SequenceFlow_03yocm5 |
+-------+------+---------+-------+----------+----------------------+----------------------+----------------------+
| 2     | 2    | 2       | Flow  | sequence | SequenceFlow_03yocm5 | StartEvent_142xowk   | Task_12g1q69         |
+-------+------+---------+-------+----------+----------------------+----------------------+----------------------+
| 2     | 3    | 1       | Task  | user     | Task_12g1q69         | SequenceFlow_03yocm5 | NULL                 |
+-------+------+---------+-------+----------+----------------------+----------------------+----------------------+

更新：您的评论

我用您评论中的XML测试了我的查询：

INSERT INTO @process(workflowXML) VALUES
('<process>
  <Event type="start" id="e1">
    <outgoing>s1</outgoing>
  </Event>
  <Flow type="sequence" id="s1" sourceRef="e1" targetRef="t1" />
  <Flow type="sequence" id="s3" sourceRef="t1" targetRef="t2" />
  <Task type="user" id="t3">
    <incoming>s2</incoming>
  </Task>
  <Task type="user" id="t1">
    <incoming>s1</incoming>
    <outgoing>s3</outgoing>
  </Task>
  <Flow type="sequence" id="s2" sourceRef="t2" targetRef="t3" />
  <Task type="service" id="t2">
    <incoming>s3</incoming>
    <outgoing>s2</outgoing>
  </Task>
</process>');

这是结果

+-------+-------+----------+----+------+------+------+---------+
| tblID | Name  | Type     | Id | In   | Out  | Rank | RevRank |
+-------+-------+----------+----+------+------+------+---------+
| 1     | Event | start    | e1 | NULL | s1   | 1    | 7       |
+-------+-------+----------+----+------+------+------+---------+
| 1     | Flow  | sequence | s1 | e1   | t1   | 2    | 6       |
+-------+-------+----------+----+------+------+------+---------+
| 1     | Task  | user     | t1 | s1   | s3   | 3    | 5       |
+-------+-------+----------+----+------+------+------+---------+
| 1     | Flow  | sequence | s3 | t1   | t2   | 4    | 4       |
+-------+-------+----------+----+------+------+------+---------+
| 1     | Task  | service  | t2 | s3   | s2   | 5    | 3       |
+-------+-------+----------+----+------+------+------+---------+
| 1     | Flow  | sequence | s2 | t2   | t3   | 6    | 2       |
+-------+-------+----------+----+------+------+------+---------+
| 1     | Task  | user     | t3 | s2   | NULL | 7    | 1       |
+-------+-------+----------+----+------+------+------+---------+

如果我正确理解逻辑，我的查询就可以正常工作：

• 事件id = e1指向s1
• 流s1指向t1
• 任务t1指向s3
• 流s3指向t2
• 任务t2指向s2
• 流s2指向t3
• 任务t3结束

我唯一看到的是事实，事实是Task t1已经是type =“ user”。如果你想要的-在任何情况下-排名最高的用户任务，你可能会带走ReverseRank-CTE并设定最终SELECT像

SELECT t.* 
FROM recCTE AS t
WHERE t.[Rank]<=ISNULL((SELECT MIN(x.[Rank]) FROM recCTE AS x WHERE x.tblID=t.tblID AND x.[Type]='user' AND x.[Name]='Task'),999)
ORDER BY t.tblID,t.[Rank]

现在，任务t1将是最后的结果，因为所有以后的等级都被过滤掉了。