snippetMinor

How to Return XML Node Ordinal, or delete node based on element value?

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

deleteordinalreturnnodexmlvalueelementbasedhow

Problem

I have an XML document similar to this:


  
    
      aslkjflaksjflkasjfkljsd
    
    
       opqiwuerl;kasdlfkjawop

I am replacing an entire ` node based on some criteria. The nodes contain a

 that I can use to identify them (pretend those are valid GUIDs please!).

Knowing the GUID, I am returning that node's XML into a variable for further processing.  Downstream, I need to be able to delete that particular node so I that I can

insert

 the modified version of the node back into the original document.

Is there a way to determine the ordinal, or another way to use a

delete/replace` method to remove a node based on an element's value?

Solution

If you have the value in a variable you can use sql:variable() in the predicate for the delete.

declare @XML xml = '

  
    
      aslkjflaksjflkasjfkljsd
    
    
       opqiwuerl;kasdlfkjawop
    
  
'

declare @Guid varchar(30) = 'aslkjflaksjflkasjfkljsd'

set @XML.modify('delete /Root/Sub/Record[Guid = sql:variable("@Guid")]')

Replacing a record with a new record would look something like this.

declare @XML xml = '

  
    
      aslkjflaksjflkasjfkljsd
      some value
    
    
       opqiwuerl;kasdlfkjawop
    
  
'

declare @Guid varchar(30) = 'aslkjflaksjflkasjfkljsd'
declare @NewRecord xml = '

  aslkjflaksjflkasjfkljsd
  some new value
'

set @XML.modify('insert sql:variable("@NewRecord") after (/Root/Sub/Record[Guid = sql:variable("@Guid")])[1]')
set @XML.modify('delete (/Root/Sub/Record[Guid = sql:variable("@Guid")])[1]')

First add the new record after the existing one and the delete the first occurrence where Guid is a match. Without [1] in the delete you will delete all occurrences, not only the first one.

Code Snippets

declare @XML xml = '
<Root>
  <Sub>
    <Record>
      <Guid>aslkjflaksjflkasjfkljsd</Guid>
    </Record>
    <Record>
       <Guid>opqiwuerl;kasdlfkjawop</Guid>
    </Record>
  </Sub>
</Root>'

declare @Guid varchar(30) = 'aslkjflaksjflkasjfkljsd'

set @XML.modify('delete /Root/Sub/Record[Guid = sql:variable("@Guid")]')

declare @XML xml = '
<Root>
  <Sub>
    <Record>
      <Guid>aslkjflaksjflkasjfkljsd</Guid>
      <Value>some value</Value>
    </Record>
    <Record>
       <Guid>opqiwuerl;kasdlfkjawop</Guid>
    </Record>
  </Sub>
</Root>'

declare @Guid varchar(30) = 'aslkjflaksjflkasjfkljsd'
declare @NewRecord xml = '
<Record>
  <Guid>aslkjflaksjflkasjfkljsd</Guid>
  <Value>some new value</Value>
</Record>'

set @XML.modify('insert sql:variable("@NewRecord") after (/Root/Sub/Record[Guid = sql:variable("@Guid")])[1]')
set @XML.modify('delete (/Root/Sub/Record[Guid = sql:variable("@Guid")])[1]')

Context

StackExchange Database Administrators Q#40039, answer score: 5

Revisions (0)

No revisions yet.