本文介紹了跟蹤 SQL Server 中 XML 節(jié)點(diǎn)的流向的處理方法，對大家解決問題具有一定的參考價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)吧！

問題描述

限時送ChatGPT賬號..

我在 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 節(jié)點(diǎn)跟蹤節(jié)點(diǎn)流.例如，我需要查詢從開始事件(Event type=start")開始并在 Task 中結(jié)束的返回 Task 節(jié)點(diǎn)用戶類型(type=user").Sample1 中的此查詢返回 Task 節(jié)點(diǎn)，其中 id=Task_0x1pjee" 并在 Sample2 中返回 Task 節(jié)點(diǎn)，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".

我認(rèn)為此查詢具有以下結(jié)構(gòu):

I think this query has this structure:

編輯 1

Sample3 具有節(jié)點(diǎn)，因此它們可以有多個傳入或傳出.

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" 開始并沿著層次結(jié)構(gòu)向下走，其中 out-data 是下一個節(jié)點(diǎn)的 Id.此行具有未定義的深度，應(yīng)在具有 type="user" 的節(jié)點(diǎn)處結(jié)束.

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" 的任務(wù)，但只有其中一個被引用為鏈上更高節(jié)點(diǎn)中的輸出數(shù)據(jù).

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 添加了反向排名，因?yàn)槟坪踔粚ψ詈笠豁?xiàng)感興趣.您可能會通過 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]

結(jié)果(您的預(yù)期輸出為 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>');

這是結(jié)果

+-------+-------+----------+----+------+------+------+---------+
| 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
任務(wù) t1 指向 s3
流 s3 指向 t2
任務(wù) t2 指向 s2
流 s2 指向 t3
任務(wù) t3 結(jié)束

我看到的唯一不同的是，Task t1 已經(jīng)是一個 type="user".如果你想要 - 在任何情況下 - 排名最高的用戶任務(wù)，你可以去掉 ReverseRank-CTE 并設(shè)置最終的 SELECT 像

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]

現(xiàn)在任務(wù) t1 將是最后一個結(jié)果，因?yàn)樗泻竺娴牡燃壎急贿^濾掉了.

Now Task t1 will be the last result, as all later ranks are filtered out.

這篇關(guān)于跟蹤 SQL Server 中 XML 節(jié)點(diǎn)的流向的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！

【網(wǎng)站聲明】本站部分內(nèi)容來源于互聯(lián)網(wǎng),旨在幫助大家更快的解決問題，如果有圖片或者內(nèi)容侵犯了您的權(quán)益，請聯(lián)系我們刪除處理，感謝您的支持！

pbootcms网站模板|日韩1区2区|织梦模板||网站源码|日韩1区2区|jquery建站特效-html5模板网

跟蹤 SQL Server 中 XML 節(jié)點(diǎn)的流向

問題描述

推薦答案

更新:您的評論

UPDATE: Your comment

相關(guān)文檔推薦