SequenceFlow_0h5l5vu

Отслеживание потока узлов XML в SQL Server

103 просмотра

2 ответа

У меня есть Processтаблица в SQL Server, как это:

введите описание изображения здесь

workflowXML столбец имеет такие значения:

Sample1:

 <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>

Sample2:

<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узлами. Например, мне нужно запросить этот возвращаемый Taskузел, который начинается с start event ( Event type="start") и заканчивается Taskuser type ( type="user"). Этот запрос в Sample1 возвращает Taskузел с id="Task_0x1pjee"и в Sample2 возвращает Taskузел с id="Task_12g1q69".

Я думаю, что этот запрос имеет такую ​​структуру:

nodesflow

Edit1

Sample3 имеет узел, так что они могут иметь более одного входящего или исходящего.

  <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>

Было бы очень полезно, если бы кто-то мог объяснить решение для этого запроса.

Благодарю.

Автор: Ali Soltani Источник Размещён: 08.11.2019 11:16

Ответы (2)


1 плюс

Решение

Надеюсь, я правильно понял:

Вы начинаете с type = "start" и идете вниз по иерархии, где out -data является идентификатором следующего узла. Эта строка имеет неопределенную глубину и должна заканчиваться на узле с type = "user" .

Ваш второй пример имеет 2 Задачи с type = "user" , но только одна из них упоминается как out-data в верхнем узле вверх по цепочке.

Мой пример будет фильтровать второй с дополнительным 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       |
+-------+-------+----------+----+------+------+------+---------+

Если я правильно понимаю логику, мой запрос работает нормально:

  • идентификатор события = 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 будет последним результатом, поскольку все последующие ранги отфильтрованы.

Автор: Shnugo Размещён: 21.08.2016 08:38

0 плюса

Я решил эту проблему с помощью этого запроса. Если есть лучший запрос, с удовольствием укажу на него.

--================== @tempProcess(result)=========================
declare @tempProcess table 
(
 ID int,
 FirstTaskID nvarchar(max)
 )
--===============================================================
declare @currentType nvarchar(max)
declare @FirstUserTaskID nvarchar(max)
declare @outgoing nvarchar(max)
declare @elementID nvarchar(max)
--================================================================
declare @ID int
declare @WorkflowXML xml
declare cur CURSOR LOCAL for
    select ID, WorkflowXML from Process 
open cur

fetch next from cur into @ID, @WorkflowXML

while @@FETCH_STATUS = 0 BEGIN    

set @currentType = '$$$$$'--defult value
set @elementID = '$$$$$'--defult value

select @outgoing = 
(
    select p.WorkflowXML.value('(process/Event[@type=''start'']/outgoing)[1]','nvarchar(max)')
    from Process as p
    where ID = @ID
)

--======================  while(Tracking flow) ========================
while (@currentType != 'user')
begin
    ------- Get target element with Flow Id (outgoing)----------------- 
    select @elementID = (
        select t.c.value('@id','nvarchar(max)')
        from Process as p
            cross apply p.WorkflowXML.nodes('process/*') AS t(c)
        where ID = @ID
        and 
        t.c.value('incoming[1]','nvarchar(max)') = @outgoing
    )
    -------------- Get Type of current element ------------------------ 
    select @currentType = 
    (
    select t.c.value('@type','nvarchar(max)')
        from Process as p
            cross apply p.WorkflowXML.nodes('process/*') AS t(c)
        where ID = @ID
        and 
        t.c.value('@id','nvarchar(max)') = @elementID
    )
    -------------- Get outgoing of current element ---------------------    
    select @outgoing = 
    (
     select t.c.value('(outgoing)[1]','nvarchar(max)')
        from Process as p
            cross apply p.WorkflowXML.nodes('process/*') AS t(c)
        where ID = @ID
        and 
        t.c.value('@id','nvarchar(max)') = @elementID
    )
    ---------------------------------------------------------------
end
--=========================  End while ========================   
if(@elementID != '$$$$$')
    begin
        set @FirstUserTaskID =  @elementID      
        -- Insert to @tempProcess
        INSERT INTO @tempProcess
        SELECT @ID,@FirstUserTaskID
    end

    --select @FirstUserTaskID
    fetch next from cur into @ID,@WorkflowXML
END

select * from @tempProcess

close cur
deallocate cur
Автор: Ali Soltani Размещён: 22.08.2016 08:48
32x32