跟踪 SQL Server 中 XML 节点的流向 [英] Tracking the flow of XML nodes in SQL Server
问题描述
我在 SQL Server 中有一个 Process 表，如下所示:
I have a Process table in SQL Server like this:
workflowXML 列具有如下值:
示例 1:
<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>
示例 2:
<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>
我想用 Flow 节点跟踪节点流.例如，我需要查询从开始事件(Event type=start")开始并在 Task 中结束的返回 Task 节点用户类型(type=user").Sample1 中的此查询返回 Task 节点，其中 id=Task_0x1pjee" 并在 Sample2 中返回 Task 节点，id=Task_12g1q69".
I want to track flow of nodes with Flow nodes. For example I need to query that return Task node that start from start event (Event type="start") and finish in Task with user type (type="user"). This query in Sample1 return Task node with id="Task_0x1pjee" and in Sample2 return Task node with id="Task_12g1q69".
我认为此查询具有以下结构:
I think this query has this structure:
编辑 1
Sample3 具有节点，因此它们可以有多个传入或传出.
Sample3 has node so that they can have more than one incoming or outgoing.
<process id="Process_1" isExecutable="false">
<Event type="start" id="StartEvent_1">
<outgoing>SequenceFlow_0qn7l4p</outgoing>
</Event>
<Flow type="sequence" id="SequenceFlow_0qnhn9s" sourceRef="Task_1jfd878" targetRef="Task_15id5tl"/>
<Task type="service" id="Task_1jfd878">
<incoming>SequenceFlow_0qn7l4p</incoming>
<outgoing>SequenceFlow_0qnhn9s</outgoing>
<outgoing>SequenceFlow_10zjx6e</outgoing>
</Task>
<Flow type="sequence" id="SequenceFlow_0qn7l4p" sourceRef="StartEvent_1" targetRef="Task_1jfd878"/>
<Flow type="sequence" id="SequenceFlow_10zjx6e" sourceRef="Task_1jfd878" targetRef="Task_0qnuy6q"/>
<Task type="user" id="Task_0qnuy6q">
<incoming>SequenceFlow_10zjx6e</incoming>
<incoming>SequenceFlow_0xiah51</incoming>
</Task>
<Task type="service" id="Task_15id5tl">
<incoming>SequenceFlow_0qnhn9s</incoming>
<outgoing>SequenceFlow_0xiah51</outgoing>
</Task>
<Flow type="sequence" id="SequenceFlow_0xiah51" sourceRef="Task_15id5tl" targetRef="Task_0qnuy6q"/>
</process>
如果有人可以解释此查询的解决方案，将会非常有帮助.
It would be very helpful if someone could explain solution for this query.
谢谢.
推荐答案
我希望我理解正确:
您从 type="start" 开始并沿着层次结构向下走，其中 out-data 是下一个节点的 Id.此行具有未定义的深度，应在具有 type="user" 的节点处结束.
You start with type="start" and walk down a hierarchy, where the out-data is the Id of the next node. This line has an undefined depth and should end at a node with type="user".
您的第二个示例有 2 个带有 type="user" 的任务，但只有其中一个被引用为链上更高节点中的输出数据.
Your second example has got 2 Tasks with type="user", but only one of them is referenced as out-data in a higher node up the chain.
我的示例将使用额外的 EXISTS 子句过滤第二个子句.
My example will filter the second with an extra EXISTS clause.
第一个 CTE DerivedTable 包含一个您也可能使用隔离的查询.它将以表格形式显示完整信息.
The first CTE DerivedTable consists of a query you might use isolated too. It will bring up the full information in table-wise format.
第二个 CTE 是递归的，从 start 开始并向下遍历链.Rank 列是链的顺序.
The second CTE is recursive, starts with the start and traverses down the chain. The column Rank is the chain's order.
第三个 CTE 添加了反向排名，因为您似乎只对最后一项感兴趣.您可能会通过 WHERE RevRank=1
The third CTE adds a reverse Rank as you seem to be interested in the last item only. You might get this by 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):
The result (your expected output is at 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 测试了我的查询:
UPDATE: Your comment
I tested my query with the XML from your comment:
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 |
+-------+-------+----------+----+------+------+------+---------+
如果我正确理解逻辑，我的查询就可以正常工作:
If I understand the logic correctly my query works just fine:
- 事件 id=e1 指向 s1
- 流 s1 指向 t1
- 任务 t1 指向 s3
- 流 s3 指向 t2
- 任务 t2 指向 s2
- 流 s2 指向 t3
- 任务 t3 结束
我看到的唯一不同的是，Task t1 已经是一个 type="user".如果你想要 - 在任何情况下 - 排名最高的用户任务，你可以去掉 ReverseRank-CTE 并设置最终的
The only thing which I see differently, is the fact, that Task t1 was a type="user" already. If you want - in any case - the highest ranked user Task, you might take away the ReverseRank-CTE and set the final SELECT like
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 将是最后一个结果，因为所有后面的等级都被过滤掉了.
Now Task t1 will be the last result, as all later ranks are filtered out.
这篇关于跟踪 SQL Server 中 XML 节点的流向的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
