All geek questions in one place

Search for multiple targets on XML nodes in SQL Server

I have a process table in SQL Server, for example:

workflow

Column

workflowXML has the following meanings:

sample1 (ProcessID = 1)

sample1

workflowXML sample 1:

 <process> <Event type="start" id="StartEvent_1" name="Start"> <outgoing>SequenceFlow_0z7u86p</outgoing> <outgoing>SequenceFlow_1onkt3z</outgoing> </Event> <task type="" id="Task_0a7vu1x" name="D"> <incoming>SequenceFlow_108ajnm</incoming> <incoming>SequenceFlow_1onkt3z</incoming> <outgoing>SequenceFlow_01clcmz</outgoing> </task> <task type="goal" id="Task_00ijt4n" name="B"> <incoming>SequenceFlow_17q1ecq</incoming> <incoming>SequenceFlow_0q9j3et</incoming> <outgoing>SequenceFlow_1ygvv8b</outgoing> <outgoing>SequenceFlow_02glv1g</outgoing> </task> <task type="" id="Task_1rnuz4y" name="A"> <incoming>SequenceFlow_1ygvv8b</incoming> <incoming>SequenceFlow_0z7u86p</incoming> <outgoing>SequenceFlow_108ajnm</outgoing> <outgoing>SequenceFlow_17q1ecq</outgoing> <outgoing>SequenceFlow_075iuj9</outgoing> </task> <task type="goal" id="Task_1d4ykor" name="E"> <incoming>SequenceFlow_01clcmz</incoming> <incoming>SequenceFlow_075iuj9</incoming> <incoming>SequenceFlow_1djp3tu</incoming> <outgoing>SequenceFlow_0q9j3et</outgoing> </task> <task type="goal" id="Task_1sembw4" name="C"> <incoming>SequenceFlow_02glv1g</incoming> <outgoing>SequenceFlow_1djp3tu</outgoing> </task> </process>

sample2 (ProcessID = 2)

sample2

workflowXML sample 2:

 <process id="Process_1" isExecutable="false"> <Event type="start" id="StartEvent_0bivq0x" name="Start"> <outgoing>SequenceFlow_0q5ik20</outgoing> <outgoing>SequenceFlow_147xk2x</outgoing> </Event> <task type="" id="Task_141buye" name="A"> <incoming>SequenceFlow_0q5ik20</incoming> <incoming>SequenceFlow_0wg37hn</incoming> <outgoing>SequenceFlow_1pvpyhe</outgoing> <outgoing>SequenceFlow_10is4pe</outgoing> </task> <task type="" id="Task_1n3p00i" name="C"> <incoming>SequenceFlow_147xk2x</incoming> <incoming>SequenceFlow_10is4pe</incoming> <outgoing>SequenceFlow_18ks1jr</outgoing> <outgoing>SequenceFlow_08gxini</outgoing> </task> <task type="goal" id="Task_0olxqpp" name="B"> <incoming>SequenceFlow_1pvpyhe</incoming> <outgoing>SequenceFlow_03eekq0</outgoing> </task> <task type="goal" id="Task_0zjgfkf" name="D"> <incoming>SequenceFlow_18ks1jr</incoming> <incoming>SequenceFlow_03eekq0</incoming> <outgoing>SequenceFlow_0wg37hn</outgoing> </task> <task type="" id="Task_1q71efy" name="E"> <incoming>SequenceFlow_08gxini</incoming> </task> </process>

Edit1 (Add Sample3)

sample3 (ProcessID = 3)

Sample3

workflowXML sample 3:

 <process> <Event type="start" id="StartEvent_1" name="Start"> <outgoing>SequenceFlow_01rkkhj</outgoing> </Event> <task type="" id="Task_1jixk79" name="A"> <incoming>SequenceFlow_01rkkhj</incoming> <incoming>SequenceFlow_1tszkq8</incoming> <outgoing>SequenceFlow_0v8wuqu</outgoing> <outgoing>SequenceFlow_14u6fh7</outgoing> <outgoing>SequenceFlow_1q4991g</outgoing> </task> <task type="" id="Task_0xwvhuo" name="B"> <incoming>SequenceFlow_0v8wuqu</incoming> <outgoing>SequenceFlow_15fmkbq</outgoing> <outgoing>SequenceFlow_0x4ykgp</outgoing> <outgoing>SequenceFlow_0f4gpf1</outgoing> </task> <task type="goal" id="Task_0qsvlob" name="G"> <incoming>SequenceFlow_0qse1xk</incoming> <incoming>SequenceFlow_16a0qvv</incoming> </task> <task type="goal" id="Task_0wtjftd" name="E"> <incoming>SequenceFlow_14u6fh7</incoming> <incoming>SequenceFlow_0z3qle8</incoming> <outgoing>SequenceFlow_0vg7sax</outgoing> <outgoing>SequenceFlow_0qse1xk</outgoing> </task> <task type="" id="Task_0c85e6p" name="F"> <incoming>SequenceFlow_0x4ykgp</incoming> <incoming>SequenceFlow_17k5zfg</incoming> <outgoing>SequenceFlow_16a0qvv</outgoing> <outgoing>SequenceFlow_0z3qle8</outgoing> </task> <task type="" id="Task_164ihwt" name="D"> <incoming>SequenceFlow_0q9hqs6</incoming> <incoming>SequenceFlow_1q4991g</incoming> <outgoing>SequenceFlow_17k5zfg</outgoing> </task> <task type="goal" id="Task_032o8jx" name="C"> <incoming>SequenceFlow_15fmkbq</incoming> <incoming>SequenceFlow_0vg7sax</incoming> <outgoing>SequenceFlow_0q9hqs6</outgoing> <outgoing>SequenceFlow_1tszkq8</outgoing> </task> <task type="goal" id="Task_0fsibap" name="H"> <incoming>SequenceFlow_0f4gpf1</incoming> </task> </process>

I need to find target nodes from the beginning of node, which are:

there is a way for them from the beginning and in this way there are no goal nodes.

The result of the query in the process table with sample1 and sample2 as follows:

 +-------+----------+-------------+ | ID | nodeName | nodeID | +-------+----------+-------------+ | 1 | B |Task_00ijt4n | +-------+----------+-------------+ | 1 | E |Task_1d4ykor | +-------+----------+-------------+ | 2 | B |Task_0olxqpp | +-------+----------+-------------+ | 2 | D |Task_0zjgfkf | +-------+----------+-------------+

It would be very helpful if someone could explain the solution for this request.

Thanks.

+1
source share
1 answer

I was curious to solve this with a recursive set-based approach, since I don't like procedural coding in T-SQL. I hope you will like it: -)

 DECLARE @process TABLE(ID INT IDENTITY, workflowXML XML); INSERT INTO @process(workflowXML) VALUES ('<process> <Event type="start" id="StartEvent_1" name="Start"> <outgoing>SequenceFlow_0z7u86p</outgoing> <outgoing>SequenceFlow_1onkt3z</outgoing> </Event> <task type="" id="Task_0a7vu1x" name="D"> <incoming>SequenceFlow_108ajnm</incoming> <incoming>SequenceFlow_1onkt3z</incoming> <outgoing>SequenceFlow_01clcmz</outgoing> </task> <task type="goal" id="Task_00ijt4n" name="B"> <incoming>SequenceFlow_17q1ecq</incoming> <incoming>SequenceFlow_0q9j3et</incoming> <outgoing>SequenceFlow_1ygvv8b</outgoing> <outgoing>SequenceFlow_02glv1g</outgoing> </task> <task type="" id="Task_1rnuz4y" name="A"> <incoming>SequenceFlow_1ygvv8b</incoming> <incoming>SequenceFlow_0z7u86p</incoming> <outgoing>SequenceFlow_108ajnm</outgoing> <outgoing>SequenceFlow_17q1ecq</outgoing> <outgoing>SequenceFlow_075iuj9</outgoing> </task> <task type="goal" id="Task_1d4ykor" name="E"> <incoming>SequenceFlow_01clcmz</incoming> <incoming>SequenceFlow_075iuj9</incoming> <incoming>SequenceFlow_1djp3tu</incoming> <outgoing>SequenceFlow_0q9j3et</outgoing> </task> <task type="goal" id="Task_1sembw4" name="C"> <incoming>SequenceFlow_02glv1g</incoming> <outgoing>SequenceFlow_1djp3tu</outgoing> </task> </process>') ,('<process id="Process_1" isExecutable="false"> <Event type="start" id="StartEvent_0bivq0x" name="Start"> <outgoing>SequenceFlow_0q5ik20</outgoing> <outgoing>SequenceFlow_147xk2x</outgoing> </Event> <task type="" id="Task_141buye" name="A"> <incoming>SequenceFlow_0q5ik20</incoming> <incoming>SequenceFlow_0wg37hn</incoming> <outgoing>SequenceFlow_1pvpyhe</outgoing> <outgoing>SequenceFlow_10is4pe</outgoing> </task> <task type="" id="Task_1n3p00i" name="C"> <incoming>SequenceFlow_147xk2x</incoming> <incoming>SequenceFlow_10is4pe</incoming> <outgoing>SequenceFlow_18ks1jr</outgoing> <outgoing>SequenceFlow_08gxini</outgoing> </task> <task type="goal" id="Task_0olxqpp" name="B"> <incoming>SequenceFlow_1pvpyhe</incoming> <outgoing>SequenceFlow_03eekq0</outgoing> </task> <task type="goal" id="Task_0zjgfkf" name="D"> <incoming>SequenceFlow_18ks1jr</incoming> <incoming>SequenceFlow_03eekq0</incoming> <outgoing>SequenceFlow_0wg37hn</outgoing> </task> <task type="" id="Task_1q71efy" name="E"> <incoming>SequenceFlow_08gxini</incoming> </task> </process>');

- request

 WITH DerivedTable AS ( SELECT prTbl.ID AS tblID ,nd.value('local-name(.)','nvarchar(max)') AS NodeName ,nd.value('@type','nvarchar(max)') AS [Type] ,nd.value('@id','nvarchar(max)') AS Id ,nd.value('@name','nvarchar(max)') AS [Name] ,nd.query('.') AS Task FROM @process AS prTbl CROSS APPLY prTbl.workflowXML.nodes('process') AS A(pr) CROSS APPLY pr.nodes('*') AS B(nd) ) ,AllIncoming AS ( SELECT tblId ,NodeName ,[Type] ,Id ,[Name] ,i.value('.','nvarchar(max)') AS [Target] FROM DerivedTable CROSS APPLY Task.nodes('task/incoming') AS A(i) WHERE NodeName='task' ) ,recCTE AS ( SELECT tblID,NodeName,[Type],Id,[Name],Task,1 AS Step,' | ' +CAST(Id AS NVARCHAR(MAX)) AS NodePath FROM DerivedTable WHERE [Type]='start' UNION ALL SELECT nxt.tblID,nxt.NodeName,nxt.[Type],nxt.Id,nxt.[Name],nxt.Task,r.Step+1,r.NodePath + ' | ' + nxt.Id FROM recCTE AS r INNER JOIN DerivedTable AS nxt ON nxt.Id IN(SELECT x.Id FROM AllIncoming AS x WHERE x.[Target] IN (SELECT o.value('.','nvarchar(max)') FROM r.Task.nodes('*/outgoing') AS A(o) ) ) WHERE r.[Type]<>'goal' AND r.NodePath NOT LIKE '%| ' + nxt.Id + '%' AND r.Step<=10 --add an appropriate depth limit to avoid recusion-depth error ) SELECT t.tblID ,t.[Name] ,t.NodePath ,t.Step ,t.Id FROM recCTE AS t WHERE t.[Type]='goal' AND t.Step<=ISNULL((SELECT MIN(x.Step) FROM recCTE AS x WHERE x.tblID=t.tblID AND x.[Type]='goal' AND x.NodeName='task'),999) ORDER BY t.tblID,t.Step

Result

 tblID Name NodePath Step Id 1 B | StartEvent_1 | Task_1rnuz4y | Task_00ijt4n 3 Task_00ijt4n 1 E | StartEvent_1 | Task_1rnuz4y | Task_1d4ykor 3 Task_1d4ykor 1 E | StartEvent_1 | Task_0a7vu1x | Task_1d4ykor 3 Task_1d4ykor 2 D | StartEvent_0bivq0x | Task_1n3p00i | Task_0zjgfkf 3 Task_0zjgfkf 2 B | StartEvent_0bivq0x | Task_141buye | Task_0olxqpp 3 Task_0olxqpp

You will find more than two results for tblID = 1, since there are different paths leading to the same node target.

UPDATE Your Example 3

My first attempt finds the shortest path to the goal. Any goal that is reached with a longer path has been filtered out. This is easy to change:

Let the final WHERE find the shortest path to the specific node by adding Id:

 WHERE t.[Type]='goal' AND t.Step<=ISNULL((SELECT MIN(x.Step) FROM recCTE AS x WHERE x.tblID=t.tblID AND x.Id=t.Id AND x.[Type]='goal' AND x.NodeName='task'),999)

This returns for all three examples:

 +-------+------+----------------------------------------------------------------------------+------+--------------+ | tblID | Name | NodePath | Step | Id | +-------+------+----------------------------------------------------------------------------+------+--------------+ | 1 | B | | StartEvent_1 | Task_1rnuz4y | Task_00ijt4n | 3 | Task_00ijt4n | +-------+------+----------------------------------------------------------------------------+------+--------------+ | 1 | E | | StartEvent_1 | Task_1rnuz4y | Task_1d4ykor | 3 | Task_1d4ykor | +-------+------+----------------------------------------------------------------------------+------+--------------+ | 1 | E | | StartEvent_1 | Task_0a7vu1x | Task_1d4ykor | 3 | Task_1d4ykor | +-------+------+----------------------------------------------------------------------------+------+--------------+ | 2 | B | | StartEvent_0bivq0x | Task_141buye | Task_0olxqpp | 3 | Task_0olxqpp | +-------+------+----------------------------------------------------------------------------+------+--------------+ | 2 | D | | StartEvent_0bivq0x | Task_1n3p00i | Task_0zjgfkf | 3 | Task_0zjgfkf | +-------+------+----------------------------------------------------------------------------+------+--------------+ | 3 | E | | StartEvent_1 | Task_1jixk79 | Task_0wtjftd | 3 | Task_0wtjftd | +-------+------+----------------------------------------------------------------------------+------+--------------+ | 3 | C | | StartEvent_1 | Task_1jixk79 | Task_0xwvhuo | Task_032o8jx | 4 | Task_032o8jx | +-------+------+----------------------------------------------------------------------------+------+--------------+ | 3 | H | | StartEvent_1 | Task_1jixk79 | Task_0xwvhuo | Task_0fsibap | 4 | Task_0fsibap | +-------+------+----------------------------------------------------------------------------+------+--------------+ | 3 | G | | StartEvent_1 | Task_1jixk79 | Task_0xwvhuo | Task_0c85e6p | Task_0qsvlob | 5 | Task_0qsvlob | +-------+------+----------------------------------------------------------------------------+------+--------------+ | 3 | G | | StartEvent_1 | Task_1jixk79 | Task_164ihwt | Task_0c85e6p | Task_0qsvlob | 5 | Task_0qsvlob | +-------+------+----------------------------------------------------------------------------+------+--------------+
+2
source

Source: https://habr.com/ru/post/1266908/

More articles:


All Articles