How to Return XML Node Ordinal, or delete node based on element 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.