CODEWITHSUNDEEP
sql-serverxml
user1969944
user1969944

Reputation: 21

Delete XML nodes in SQL server based on conditions

I have xml that looks like

<MeasureSet>
    <MeasureSetType val_type="name">Variant</MeasureSetType>
    <Measure>
      <MeasureType val_type="name">single nucleotide variant</MeasureType>
      <AttributeSet>
        <MeasureAttributeType val_type="name">HGVS</MeasureAttributeType>
        <Attribute>NM_000054.4:c.838dupT</Attribute>
      </AttributeSet>
      <Citation>
        <CitationType val_type="name">general</CitationType>
        <ID Source="PubMed">10820168</ID>
      </Citation>
      <Citation>
        <CitationType val_type="name">general</CitationType>
        <ID Source="PubMed" />
      </Citation>
      <Citation>
        <CitationType val_type="name">general</CitationType>
        <ID Source="PubMed">9773787</ID>
      </Citation>
      <Citation>
        <CitationType val_type="name">general</CitationType>
        <ID Source="PubMed">18726898</ID>
      </Citation>
      <Citation>
        <CitationType val_type="name">general</CitationType>
        <ID Source="PubMed" />
      </Citation>
    </Measure>
  </MeasureSet>

I want to delete the Citation element nodes that have Citation/ID/@Source = "PubMed" and Citation/ID is empty

This deletes only the ID element, but the correct ones

SET @myBlob.modify('delete //Citation/ID[@Source = ''PubMed''
      and string-length(string(number(.))) = 0]')

and this deletes all Citation elements

SET @myBlob.modify('delete //Citation[ID/@Source = ''PubMed''
      and string-length(string(number(.))) = 0]')

Seems there should be an easy solution I'm not hitting on. Any suggestions? Thanks

Upvotes: 2

Views: 2951

Answers (1)

AakashM
AakashM

Reputation: 63378

Your second one is checking the length of the wrong element - it's looking at (.), which is here the Citation element (because it's in a predicate on the Citation element). Use instead

SET @myBlob.modify('
      delete //Citation[ID/@Source = ''PubMed'' 
                        and string-length(string(number(ID[1]))) = 0]')

where I have changed . to ID[1]. The [1] is needed because "'number()' requires a singleton (or empty sequence)".

Upvotes: 2

Related Questions