This is not the first time you are trying to help me out. Thank you very much.

I should have mentioned that my XML is validated against a schema that looks something like this:

CREATE XML SCHEMA COLLECTION MySchema AS N'<?xml version="1.0"?>

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified" attributeFormDefault="unqualified">

<xs:element name="Attribute">

<xs:complexType>

<xs:sequence>

<xs:element name="A1" type="xs:string" minOccurs="0" maxOccurs="1"></xs:element>

<xs:element name="A2" type="xs:string" minOccurs="0" maxOccurs="1"></xs:element>

<xs:element name="A3" type="xs:string" minOccurs="0" maxOccurs="1"></xs:element>

<xs:element name="A4" type="xs:string" minOccurs="0" maxOccurs="1"></xs:element>

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:schema>

';

Now, if we run the query:

USE tempdb;

GO

SET NOCOUNT ON;

DECLARE @TXML XML(MySchema) = '<Attribute>

<A1>6529</A1>

<A2>6529</A2>

<A4>6529</A4>

</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") as last into (/Attribute)[1]');

SELECT @TXML ;

This will result in an error message which is expected due to the sequence:

XML Validation: Unexpected element(s): A3. Location: /*:Attribute[1]/*:A3[1]

What I'm looking for is query that inserts A3 in between A2 and A4.

Also, nodes are optional as you can see in the schema, so

if we had: '<Attribute><A4>6529</A4></Attribute>'

I'd expect A3 to be inserted before A4

if we had: '<Attribute><A1>6529</A1></Attribute>'

I'd expect A3 to be inserted after A1

Hope this makes sense? Thank you again!!!!