ali soltani ali soltani - 4 months ago 18
SQL Question

get attribute value of XML child node with where condition on parent node

I have a

Process
table in sql server database like this:

Proccess Table

workflowXML column has values like this:

<Tasks>
<Task type="start" id="Task_038517r">
<TaskUsers>
<TaskUser RoleName="User"/>
</TaskUsers>
</Task>
<Task type="final" id="Task_1sytah6">
<TaskUsers>
<TaskUser RoleName="Admin"/>
</TaskUsers>
</Task>
</Tasks>


I need to sql query for get
RoleName
in
Task
nodes that
type
of
Task
is
start
.
I test this query:

select m.c.value('@RoleName','varchar(max)') as RoleName
from Process as p
outer apply
p.WorkflowXML.nodes('/Tasks/Task/TaskUsers/TaskUser') as m(c)
where
WorkflowXML.exist('/Tasks/Task[@type="start"]') = 1


but
WorkflowXML.exist
is not correct and return all of
RoleNames
in xml columns.

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

Thanks.

Answer

It was much better to include your filter directly into the XPath:

select m.c.value('@RoleName','varchar(max)') as RoleName
from Process as p
outer apply p.WorkflowXML.nodes('/Tasks/Task[@type="start"]/TaskUsers/TaskUser') as  m(c)

Your approach with WHERE could get quite slow with bigger amounts/structures...