sqlsql-serverxmltraversal
Multiple goal search on XML nodes in SQL Server
I have a Process table in SQL Server like this:
workflowXML column has values like this:
sample1 (ProcessID=1)
workflowXML of sample1:
<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)
workflowXML of sample2:
<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)
workflowXML of sample3:
<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 goal nodes from start node that:
there is a path for them from start and there are no goal nodes in this path.
Result of query in process table with sample1 and sample2 is like this:
+-------+----------+-------------+
| 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 solution for this query.
Thanks.
Solution
I was curious to solve this with a set-based recursive approach as I do not like procedural coding in T-SQL. Hope you 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>');
--the query
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
The 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 find more than two results for tblID=1 as there are differing paths leading to the same goal node.
UPDATE Your example 3
My first attempt finds the shortest path to a goal. Any goal, which is reached with a longer path, was filtered. This is easy to change:
Let the final WHERE find the shortest path to a specific node by adding the 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 |
+-------+------+----------------------------------------------------------------------------+------+--------------+
- How to properly delete data from a table and chain the deletion
- Find procedure name in all other procedures
- SQL-How to Insert Row Without Auto incrementing a ID Column?
- Why can't I use an alias in a DELETE statement?
- How do I perform a GROUP BY on an aliased column in SQL Server?
- Running multiple SQL-Statements with Python
- Using rangeBetween considering months rather than days in PySpark
- How to find databases which accessible to me in Sql server?
- data return incorrect using left join with 2 tables
- Repeat rows specified number of times in PostgreSQL
- How to visualize database tables in postgresql using pgAdmin?
- Odata override behaviour of orderby
- Unable to open BCP host data-file
- How to join parent object with nested array using OpenJson SQL
- batch update a column using sql
- Queries using LIKE wildcards in sql server
- Conditional aggregation in SQL: display condition-dependent count in percentage?
- Parsing JSON with Oracle SQL without knowing incoming field names
- In a standard MS Access SQL query output that does not have any aliases, how do I replace the full names by their "first-letters" aliases?
- Convert SQL containing GROUP BY and SUM() to active record in CodeIgniter
- output sql data to html with hyperlink
- Update all column names to be lowercase
- SQL Server pivoting on data without an aggregate
- Flagging a row in new column based on conditions on previous and current rows
- replace or remove characters and then cast to decimal
- Export specific rows from a PostgreSQL table as INSERT SQL script
- Query to get only numbers from a string
- SQL grouping result to half hour
- table is specified twice both as a target for INSERT and as separate source of data
- SQL to JSON Need Bracket for Array that can have multiple rows