在SQL Server中使用动态父节点获取XML子节点值

I have a Process table in SQL Server like this:

我在SQL Server中有这样一个过程表:

workflowXML column has values like this:

workflowXML列有如下值:

<bpmn:definitions xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
      xmlns:bpmn="http://www.omg.org/spec/BPMN/20100524/MODEL" 
      xmlns:bpmndi="http://www.omg.org/spec/BPMN/20100524/DI" 
      xmlns:dc="http://www.omg.org/spec/DD/20100524/DC" 
      xmlns:di="http://www.omg.org/spec/DD/20100524/DI" 
      id="Definitions_1" targetNamespace="http://bpmn.io/schema/bpmn">
     <bpmn:process id="Process_1" isExecutable="false">
         <bpmn:startEvent id="StartEvent_1rin5au">
             <bpmn:outgoing>SequenceFlow_1q36672</bpmn:outgoing>
         </bpmn:startEvent>   
         <bpmn:userTask id="UserTask_038517r" name="addRequest">
             <bpmn:incoming>SequenceFlow_1q36672</bpmn:incoming>
             <bpmn:outgoing>SequenceFlow_0dpq0oe</bpmn:outgoing>
         </bpmn:userTask>      
         <bpmn:serviceTask id="ServiceTask_17e1u09" name="checkRequest">
             <bpmn:incoming>SequenceFlow_1q36672</bpmn:incoming>
             <bpmn:outgoing>SequenceFlow_1233bp0</bpmn:outgoing>
         </bpmn:serviceTask>        
     </bpmn:process>    
</bpmn:definitions>

I need a SQL query to get id node that incoming value be equal with outgoing value in bpmn:startEvent node.

我需要一个SQL查询来获得id节点，使传入值与bpmn:startEvent节点中的传出值相等。

I test this subquery:

我测试这个子查询:

;WITH XMLNAMESPACES('http://www.omg.org/spec/BPMN/20100524/MODEL' AS bpmn) 
select 
    p.workflowXML.value('(bpmn:definitions/bpmn:process/bpmn:userTask/bpmn:incoming)[1]', 'nvarchar(max)')
from 
    Process as p

But this code only returns result for bpmn:userTask. I used other subquery:

但是这段代码只返回bpmn:userTask的结果。我用其他的子查询:

;WITH XMLNAMESPACES('http://www.omg.org/spec/BPMN/20100524/MODEL' AS bpmn) 
select 
    p.workflowXML.value('(bpmn:definitions/bpmn:process/../bpmn:incoming)[1]', 'nvarchar(max)')
from 
    Process as p

But it returns null.

但它返回null。

My query is like this:

我的问题是这样的:

declare @SequenceFlow nvarchar(max)
;WITH XMLNAMESPACES('http://www.omg.org/spec/BPMN/20100524/MODEL' AS bpmn) 
select @SequenceFlow = 
(
SELECT p.workflowXML.value('(/bpmn:definitions/bpmn:process/bpmn:startEvent/bpmn:outgoing)[1]','nvarchar(max)') 
from Process as p
where ID = 1
)

;WITH XMLNAMESPACES('http://www.omg.org/spec/BPMN/20100524/MODEL' AS bpmn) 
select p.workflowXML.value('(bpmn:definitions/bpmn:process/../bpmn:incoming)[1]','nvarchar(max)')
from Process as p
where p.workflowXML.value('(bpmn:definitions/bpmn:process/../bpmn:incoming)[1]','nvarchar(max)') = @SequenceFlow

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

如果有人能解释这个问题的解决方案，那将非常有帮助。

Thanks.

谢谢。

1 个解决方案

#1

You can use * to reference element of arbitrary name :

您可以使用*来引用任意名称的元素:

;WITH XMLNAMESPACES('http://www.omg.org/spec/BPMN/20100524/MODEL' AS bpmn) 
select t.c.value('@id','nvarchar(max)')
from Process as p
    cross apply p.DiagramXML.nodes('bpmn:definitions/bpmn:process/*') AS t(c)
where t.c.value('bpmn:incoming[1]', 'nvarchar(max)') = @SequenceFlow

Notice that cross apply is used to shred XML data at child of bpmn:process element level. This allows the query to return multiple rows from single XML column data.

注意，交叉应用程序用于在bpmn的子节点上分解XML数据:流程元素级别。这允许查询从单个XML列数据返回多行。

#1