Find child where parent IS NOT a certain element
I'd like to find an empty organisation element where it is not the immediate child of a parentProblem element.
Like so..
select * from Audit.PatientAudit pa
where pa.Befor开发者_Python百科eXml.exist('//*:organisation[not(../*:parentProblem)]') = 1
But it doesn't seem to work, any ideas?
declare @T table(BeforeXml xml)
insert into @T values
('<root>
<parentProblem>
<organisation/>
</parentProblem>
</root>'),
('<root>
<anotherProblem>
<organisation/>
</anotherProblem>
</root>'),
('<root>
<anotherProblem>
<organisation ID="1"/>
</anotherProblem>
</root>')
select *
from @T pa
where pa.BeforeXml.exist('//organisation[local-name(..)!="parentProblem" and count(@*)=0]') = 1
精彩评论