Delete XML nodes in SQL server based on conditions

Question

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

AakashM · Accepted Answer · 2013-01-11 12:53:45Z

2

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)".

answered Jan 11, 2013 at 12:53

AakashM

63.5k17 gold badges154 silver badges190 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Delete XML nodes in SQL server based on conditions

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related