How to Return XML Node Ordinal, or delete node based on element value? I have an XML document similar to this:

&lt;Root&gt;
  &lt;Sub&gt;
    &lt;Record&gt;
      &lt;Guid&gt;aslkjflaksjflkasjfkljsd&lt;/Guid&gt;
    &lt;/Record&gt;
    &lt;Record&gt;
       &lt;Guid&gt;opqiwuerl;kasdlfkjawop&lt;/Guid&gt;
    &lt;/Record&gt;
  &lt;/Sub&gt;
&lt;/Root&gt;


I am replacing an entire &lt;Record&gt; node based on some criteria.  The &lt;Record&gt; nodes contain a &lt;Guid&gt; 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?
If you have the value in a variable you can use sql:variable() in the predicate for the delete.

declare @XML xml = '
&lt;Root&gt;
  &lt;Sub&gt;
    &lt;Record&gt;
      &lt;Guid&gt;aslkjflaksjflkasjfkljsd&lt;/Guid&gt;
    &lt;/Record&gt;
    &lt;Record&gt;
       &lt;Guid&gt;opqiwuerl;kasdlfkjawop&lt;/Guid&gt;
    &lt;/Record&gt;
  &lt;/Sub&gt;
&lt;/Root&gt;'

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 = '
&lt;Root&gt;
  &lt;Sub&gt;
    &lt;Record&gt;
      &lt;Guid&gt;aslkjflaksjflkasjfkljsd&lt;/Guid&gt;
      &lt;Value&gt;some value&lt;/Value&gt;
    &lt;/Record&gt;
    &lt;Record&gt;
       &lt;Guid&gt;opqiwuerl;kasdlfkjawop&lt;/Guid&gt;
    &lt;/Record&gt;
  &lt;/Sub&gt;
&lt;/Root&gt;'

declare @Guid varchar(30) = 'aslkjflaksjflkasjfkljsd'
declare @NewRecord xml = '
&lt;Record&gt;
  &lt;Guid&gt;aslkjflaksjflkasjfkljsd&lt;/Guid&gt;
  &lt;Value&gt;some new value&lt;/Value&gt;
&lt;/Record&gt;'

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.