Add where condition for check attribute value in XML nodes

Ali Soltani

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 that are first node can be seen from start event (Event type="start"). NodePath explains path from start event to target node. Result of this query is like this:

I need to new query that select from result where RoleName

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

exists in output of SplitbyDelimiter function.

SplitbyDelimiter function splits a string by ,. For example:

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

returns

I edited DerivedTable part like 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]
          --,t.c.value('@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 t.c.value('@RoleName','nvarchar(max)') in (select * from SplitbyDelimiter('myFirstRole,mySecondRole',','))
)

But It is not correct and return 216 records!(reason:Existence extra columns with different values).Also I added inner join result in end of query but this is not working 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]
          ,t.c.value('@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 return too many same records and run time for it is 19s!!. It is not good query.

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

Shnugo

Your query

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]
          ,t.c.value('@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'll get all TaksUser elements. Hence the many rows... You might 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 work down your WorkFlow would break, if you add rows to the original resultset!

What you can try:

Add the information as additional column and let the rest untouched. In my query I do this twice (TaskUsers and RoleNames), as I do not really 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 looks like this

What ever you want to do with this additional information, any filter you want to apply, should be done as the last step...

But - to be honest - you've reached the limits of SQL-Server... This is not the right tool. Sooner or later there will be some more business logic to implement... If this has to be done within SQL Server you might think of a CLR-method...

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-04-10

Comments

0 comments

TOP Ranking

Article