Good catch with the node value Arthur, don't think it merits a dynamic sql though as one can easily encode the value with a simple FOR XML statement.
😎
/* Simple encoding statement using FOR XML
*/
SELECT @nval = (
SELECT
'' + @nval
FOR XML PATH('')
);
The reason for querying the xml schema collection views is simply the fact that one cannot rely on the alphabetical order of the node names in a sequence, hence the schema definition is the only reliable source. The query sample is very simple though, real life applications are normally quite complicated and often it is simpler to have either a table or a hard coded representation.
This code sample has a sequence of non alphabetical order and a node value containing "illegal" characters.
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="A4" type="xs:string" minOccurs="0" maxOccurs="1"></xs:element>
<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:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
';
GO
DECLARE @TXML XML = '<Attribute>
<A4>6700</A4>
<A1>6730</A1>
</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);
/* Simple encoding statement using a
CTE and FOR XML
*/
;WITH NVALUE_STR AS
(
SELECT @nval AS XSTR
)
SELECT @nval = (
SELECT
'' + NV.XSTR
FROM NVALUE_STR NV
FOR XML PATH('')
);
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>
<A4>6700</A4>
<A1>6730</A1>
<A3>New </>?;:value</A3>
</Attribute>