Add where condition to check attribute value in XML nodes

Question

I have this SQL query:

DECLARE @process TABLE(ID INT IDENTITY, workflowXML XML, Name nvarchar(250),Description nvarchar(MAX)); INSERT INTO @process(workflowXML,Name,Description) VALUES ('<process> <Event type="start" id="StartEvent_1"> <outgoing>SequenceFlow_1uj46ib</outgoing> </Event> <Task type="service" id="Task_0uurv2v"> <incoming>SequenceFlow_1uj46ib</incoming> <outgoing>SequenceFlow_051szgj</outgoing> </Task> <Task type="user" id="Task_1yh7nak"> <incoming>SequenceFlow_051szgj</incoming> <TaskUsers> <TaskUser RoleName="myFirstRole" /> <TaskUser RoleName="mySecondRole" /> </TaskUsers> </Task> </process>' ,'Process1' ,'test Process 1') ,('<process> <Event type="start" id="StartEvent_1" name="Start"> <outgoing>SequenceFlow_0z7u86p</outgoing> <outgoing>SequenceFlow_1onkt3z</outgoing> </Event> <Task type="service" id="Task_0a7vu1x"> <incoming>SequenceFlow_108ajnm</incoming> <incoming>SequenceFlow_1onkt3z</incoming> <outgoing>SequenceFlow_01clcmz</outgoing> </Task> <Task type="user" id="Task_00ijt4n"> <incoming>SequenceFlow_17q1ecq</incoming> <incoming>SequenceFlow_0q9j3et</incoming> <outgoing>SequenceFlow_1ygvv8b</outgoing> <outgoing>SequenceFlow_02glv1g</outgoing> </Task> <Task type="service" id="Task_1rnuz4y"> <incoming>SequenceFlow_1ygvv8b</incoming> <incoming>SequenceFlow_0z7u86p</incoming> <outgoing>SequenceFlow_108ajnm</outgoing> <outgoing>SequenceFlow_17q1ecq</outgoing> <outgoing>SequenceFlow_075iuj9</outgoing> </Task> <Task type="user" id="Task_1d4ykor"> <incoming>SequenceFlow_01clcmz</incoming> <incoming>SequenceFlow_075iuj9</incoming> <incoming>SequenceFlow_1djp3tu</incoming> <outgoing>SequenceFlow_0q9j3et</outgoing> <TaskUsers> <TaskUser RoleName="myFirstRole" /> <TaskUser RoleName="mySecondRole" /> </TaskUsers> </Task> <Task type="user" id="Task_1sembw4"> <incoming>SequenceFlow_02glv1g</incoming> <outgoing>SequenceFlow_1djp3tu</outgoing> <TaskUsers> <TaskUser RoleName="myFirstRole" /> <TaskUser RoleName="mySecondRole" /> </TaskUsers> </Task> </process>' ,'Process2' ,'test Process 2') ,('<process> <Event type="start" id="StartEvent_0bivq0x"> <outgoing>SequenceFlow_0q5ik20</outgoing> <outgoing>SequenceFlow_147xk2x</outgoing> </Event> <Task type="service" id="Task_141buye"> <incoming>SequenceFlow_0q5ik20</incoming> <incoming>SequenceFlow_0wg37hn</incoming> <outgoing>SequenceFlow_1pvpyhe</outgoing> <outgoing>SequenceFlow_10is4pe</outgoing> </Task> <Task type="service" id="Task_1n3p00i" > <incoming>SequenceFlow_147xk2x</incoming> <incoming>SequenceFlow_10is4pe</incoming> <outgoing>SequenceFlow_18ks1jr</outgoing> <outgoing>SequenceFlow_08gxini</outgoing> </Task> <Task type="user" id="Task_0olxqpp"> <incoming>SequenceFlow_1pvpyhe</incoming> <outgoing>SequenceFlow_03eekq0</outgoing> </Task> <Task type="user" id="Task_0zjgfkf"> <incoming>SequenceFlow_18ks1jr</incoming> <incoming>SequenceFlow_03eekq0</incoming> <outgoing>SequenceFlow_0wg37hn</outgoing> <TaskUsers> <TaskUser RoleName="mythirdRole" /> </TaskUsers> </Task> <Task type="service" id="Task_1q71efy"> <incoming>SequenceFlow_08gxini</incoming> </Task> </process>' ,'Process3' ,'test Process 3') ; 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.query('.') AS Task ,prTbl.Name AS [Name] ,prTbl.Description AS [Description] 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] ,[Description] ,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],[Description],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.Description,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]<>'user' AND r.NodePath NOT LIKE '%| ' + nxt.Id + '%' AND r.Step<=10 ) select a.tblID as ProcessID,[Name],[Description],a.NodePath,a.Id as TaskID from ( SELECT t.tblID ,t.[Name] ,t.Description ,t.NodePath ,t.Id FROM recCTE AS t WHERE t.[Type]='user' AND t.Step<=ISNULL((SELECT MIN(x.Step) FROM recCTE AS x WHERE x.tblID=t.tblID AND x.[Type]='user' AND x.NodeName='Task'),10000) ) a ORDER BY a.tblID 

This returns Task nodes, which are the first nodes that can be seen from the start event ( Event type="start" ). NodePath explains the path from the start of the event to the target node. The result of this query is as follows:

I need a new query that selects from the result where RoleName

 <TaskUsers> <TaskUser RoleName="myFirstRole" /> <TaskUser RoleName="mySecondRole" /> </TaskUsers> 

exists in the output of the SplitbyDelimiter function.

SplitbyDelimiter function splits the string into SplitbyDelimiter For instance:

  select * from SplitbyDelimiter('myFirstRole,mySecondRole',',') 

returns

I edited the DerivedTable part as follows:

 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.query('.') AS Task ,prTbl.Name AS [Name] ,prTbl.Description AS [Description] --,tcvalue('@RoleName','nvarchar(max)') as [Role] FROM @process AS prTbl CROSS APPLY prTbl.workflowXML.nodes('process') AS A(pr) CROSS APPLY pr.nodes('*') AS B(nd) CROSS APPLY prTbl.workflowXML.nodes('process/Task/TaskUsers/TaskUser') AS t(c) where tcvalue('@RoleName','nvarchar(max)') in (select * from SplitbyDelimiter('myFirstRole,mySecondRole',',')) ) 

But this is incorrect and returns 216 records! (reason: the presence of additional columns with different values). I also added inner join result at the end of the query, but this does not work fine.

Update

I used this 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.query('.') AS Task ,prTbl.Name AS [Name] ,prTbl.Description AS [Description] ,tcvalue('@RoleName','nvarchar(max)') as [Role] FROM @process AS prTbl CROSS APPLY prTbl.workflowXML.nodes('process') AS A(pr) CROSS APPLY pr.nodes('*') AS B(nd) CROSS APPLY prTbl.workflowXML.nodes('process/Task/TaskUsers/TaskUser') AS t(c) ) ,AllIncoming AS ( SELECT tblId ,NodeName ,[Type] ,Id ,[Name] ,[Description] ,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],[Description],[Role],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.Description,nxt.Role,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]<>'user' AND r.NodePath NOT LIKE '%| ' + nxt.Id + '%' AND r.Step<=10 ) select a.tblID as ProcessID,[Name],[Description],a.NodePath,a.Id as TaskID,count(*) as records from ( SELECT t.tblID ,t.[Name] ,t.Description ,t.NodePath ,t.Id ,t.Role FROM recCTE AS t WHERE t.[Type]='user' AND t.Step<=ISNULL((SELECT MIN(x.Step) FROM recCTE AS x WHERE x.tblID=t.tblID AND x.[Type]='user' AND x.NodeName='Task'),10000) ) a INNER JOIN [dbo].[SplitbyDelimiter]('myFirstRole,mySecondRole',',') r ON r.TheField = a.Role group by a.tblID,[Name],[Description],a.NodePath,a.Id ORDER BY a.tblID 

This returns:

This query returns too many records and the runtime for it: 19s !!. This is not a good request.

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

Answer 1

Your request

I used this 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.query('.') AS Task ,prTbl.Name AS [Name] ,prTbl.Description AS [Description] ,tcvalue('@RoleName','nvarchar(max)') as [Role] FROM @process AS prTbl CROSS APPLY prTbl.workflowXML.nodes('process') AS A(pr) CROSS APPLY pr.nodes('*') AS B(nd) CROSS APPLY prTbl.workflowXML.nodes('process/Task/TaskUsers/TaskUser') AS t(c) ) 

uses the wrong path in the last CROSS APPLY . You will receive all TaksUser items . Consequently, many lines ... You can change this to

 [...] CROSS APPLY pr.nodes('*') AS B(nd) OUTER APPLY nd.nodes('TaskUsers/TaskUser') AS t(c) 

to collect all TaskUser elements below the current node ...

But:

The whole approach to working with your WorkFlow will break if you add rows to the original result set!

What you can try:

Add the information as an additional column , and the rest will remain untouched. In my request, I do this twice (TaskUsers and RoleNames), since I really don't understand what you want to do with this:

 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.query('.') AS Task ,prTbl.Name AS [Name] ,prTbl.Description AS [Description] ,nd.query('./TaskUsers/TaskUser') AS TaskUsers ,nd.query('./TaskUsers/TaskUser').query('for $rn in /TaskUser return string($rn/@RoleName)').value('.','nvarchar(max)') AS RoleNames 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] ,[Description] ,TaskUsers ,RoleNames ,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],[Description],TaskUsers,RoleNames,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.Description,nxt.TaskUsers,nxt.RoleNames,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]<>'user' AND r.NodePath NOT LIKE '%| ' + nxt.Id + '%' AND r.Step<=10 ) select a.tblID as ProcessID,[Name],[Description],TaskUsers,RoleNames,a.NodePath,a.Id as TaskID from ( SELECT t.tblID ,t.[Name] ,t.Description ,t.TaskUsers ,t.RoleNames ,t.NodePath ,t.Id FROM recCTE AS t WHERE t.[Type]='user' AND t.Step<=ISNULL((SELECT MIN(x.Step) FROM recCTE AS x WHERE x.tblID=t.tblID AND x.[Type]='user' AND x.NodeName='Task'),10000) ) a ORDER BY a.tblID 

The result is as follows

What would you like to do with this additional information, any filter that you want to apply should be performed as the last step ...

But to be honest, you have reached the limits of SQL-Server ... This is not the most suitable tool. Sooner or later, another business logic will be implemented ... If you need to do this in SQL Server, you can think of a CLR method ...