1

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.BeforeXml.exist('//*:organisation[not(../*:parentProblem)]') = 1

But it doesn't seem to work, any ideas?

Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
Dog Ears
  • 9,637
  • 5
  • 37
  • 54

1 Answers1

2
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
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
  • +1 @Mikael Eriksson - Cheers but I worded my question slightly wrong. All the organsisation elements are empty but I want to find elements where there are no attributes! I know what attributes their are (only two) so I can explicitly check for there abscense with `not(@guid)` for example, but for completeness, is there a nicer way? – Dog Ears May 26 '11 at 09:05
  • @Dog Ears - Updated answer. Did I understand you correctly, only rows with 0 attributes? – Mikael Eriksson May 26 '11 at 10:26