删除基于属性值从SQL Server 2008 R2的表列XML节点(Delete xml node

我有一个XML结构如下

<DifficultyRule xmlns="urn:gjensidige:processguide:201201">
 <Id>fc39f423-05c0-4de9-ae46-12fe3c0c279b</Id>
 <Code>5595e558-7d10-4767-86dc-5d16f24b8151_Code</Code>
 <Author />
 <Updated>9/5/2012</Updated>
 <Sequence>0</Sequence>
 <FromControls>
 <Control>
  <Code>oiuyui</Code>
  <Id>70579cbe-c0b5-4b49-a7b8-6201af388f59</Id>
  <FilterValues>
     <FilterValue xmlns:p5="urn:gjensidige:processguide:201201" p5:Id="b897f3ac-b40f-4b96-b438-eb156a26457e" p5:Code="e" p5:LookupId="3fa26ce7-4031-4e41-92cb-50d8ce56d262" />
   </FilterValues>
 </Control>
</FromControls>
<DifficultyCode>Red</DifficultyCode>
</DifficultyRule>

我试图删除FilterValue节点中的XPath

/qn:DifficultyRule/qn:FromControls/qn:Control/qn:FilterValues/qn:FilterValue

基于该值p5:Id归属，但我很困惑，为什么节点没有被清除。

下面是我使用的脚本：

declare @lookupvalueId varchar(50)
declare @ruleId varchar(50)

set @lookupvalueId = 'b897f3ac-b40f-4b96-b438-eb156a26457e'
set @ruleId = 'fc39f423-05c0-4de9-ae46-12fe3c0c279b'

;WITH XMLNAMESPACES ('urn:gjensidige:processguide:201201' as qn)
update pdr_processdefinitionrule
set PDR_RuleXml.modify('delete (/qn:DifficultyRule/qn:FromControls/qn:Control/qn:FilterValues/qn:FilterValue[@Id=sql:variable("@lookupvalueId")])')
where pdr_guid = @ruleId

我在想什么？任何指导，可以理解

好吧，既然你的XML属性id与前缀XML命名空间p5 ，还必须声明和使用第二XML命名空间在你的代码：

declare @lookupvalueId varchar(50)
declare @ruleId varchar(50)

set @lookupvalueId = 'b897f3ac-b40f-4b96-b438-eb156a26457e'
set @ruleId = 'fc39f423-05c0-4de9-ae46-12fe3c0c279b'

;WITH XMLNAMESPACES ('urn:gjensidige:processguide:201201' as qn,
                     'urn:gjensidige:processguide:201201' as p5)
update 
    pdr_processdefinitionrule
set 
    PDR_RuleXml.modify('delete (/qn:DifficultyRule/qn:FromControls/qn:Control/qn:FilterValues/qn:FilterValue[@p5:Id=sql:variable("@lookupvalueId")])')
where 
    pdr_guid = @ruleId

请参阅该第二XML命名空间定义p5 ？看到使用该XML命名空间中的qn:FilterValue[@p5:Id=sql:variable("@lookupvalueId")]的表达？