As I mentioned before, often the XML must be interrogated to determine the placement of the inserted node. This sample checks both the XML to be modified and the XML Schema Collection to correctly place the node insert.
😎
USE tempdb;
GO
SET NOCOUNT ON;
GO
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>
';
GO
DECLARE @TXML XML = '<Attribute>
<A1>6700</A1>
<A4>6730</A4>
</Attribute>';
DECLARE @XMLSCHEMA_NAME NVARCHAR(128) = N'MySchema'
DECLARE @NNODE VARCHAR(100) = 'A3';
DECLARE @nval VARCHAR(100) = 'New value';
DECLARE @Nt VARCHAR(200) = '<{{@NNODE}}>{{@NVAL}}</{{@NNODE}}>';
DECLARE @NXML XML;
DECLARE @XSTNODE VARCHAR(100) = '';
;WITH XML_SCHEMA_STRUCTURE AS
(
SELECT
XSC.name
,XCP.placement_id
FROM sys.xml_schema_collections XC
INNER JOIN sys.xml_schema_components XSC
ON XC.xml_collection_id = XSC.xml_collection_id
INNER JOIN sys.xml_schema_component_placements XCP
ON XSC.xml_component_id = XCP.placed_xml_component_id
WHERE XC.name = @XMLSCHEMA_NAME
AND XSC.name IS NOT NULL
)
,XML_STRUCTURE AS
(
SELECT
TNODE.DATA.value('local-name(.)','VARCHAR(100)') AS XNODE
FROM @TXML.nodes('/Attribute/*') AS TNODE(DATA)
)
,NODE_TO_INSERT AS
(
SELECT
XSS.name
,XSS.placement_id
FROM XML_SCHEMA_STRUCTURE XSS
WHERE XSS.name = @NNODE
)
SELECT
@XSTNODE = (SELECT TOP(1) XSS.name
FROM XML_SCHEMA_STRUCTURE XSS
LEFT OUTER JOIN XML_STRUCTURE XS
ON XSS.name = XS.XNODE
WHERE XSS.placement_id < (SELECT TOP(1) placement_id
FROM NODE_TO_INSERT NTI)
AND XS.XNODE IS NOT NULL
ORDER BY XSS.placement_id DESC);
SELECT @NXML = CONVERT(XML,REPLACE(REPLACE(@NT,'{{@NNODE}}',@NNODE),'{{@NVAL}}',@NVAL),0);
IF (@XSTNODE IS NOT NULL)
BEGIN
SET @TXML.modify('insert sql:variable("@NXML") after (/Attribute/*[local-name() = sql:variable("@XSTNODE")])[1]');
END
IF (@XSTNODE IS NULL)
BEGIN
SET @TXML.modify('insert sql:variable("@NXML") as first into (/Attribute)[1]');
END
SELECT @TXML;
GO
DROP XML SCHEMA COLLECTION MySchema;
GO
Results
<Attribute>
<A1>6700</A1>
<A3>New value</A3>
<A4>6730</A4>
</Attribute>