The XML modify/insert has 4 positioning directives, as first,as last, after and before, changing from as last to after for the correct positioning, note the slight difference in the syntax.

Often the XML must be interrogated prior to modification.

😎

USE tempdb;

GO

SET NOCOUNT ON;

DECLARE @TXML XML = '<Attribute>

<A1>6529</A1>

<A2>6529</A2>

</Attribute>';

DECLARE @NNODE VARCHAR(100) = 'A3';

DECLARE @nval VARCHAR(100) = 'New value';

DECLARE @Nt VARCHAR(200) = '<{{@NNODE}}>{{@NVAL}}</{{@NNODE}}>';

DECLARE @NXML XML;

SELECT @NXML = CONVERT(XML,REPLACE(REPLACE(@NT,'{{@NNODE}}',@NNODE),'{{@NVAL}}',@NVAL),0);

SET @TXML.modify('insert sql:variable("@NXML") after (/Attribute/A2)[1]');

SELECT @TXML ;