HoneyHoney

IF EXISTS (select 1 from sys.objects WHERE type = 'FN' AND name = 'removeXmlNode')
DROP FUNCTION dbo.removeXmlNode
GO
CREATE FUNCTION dbo.removeXmlNode(@XMLString NVARCHAR(MAX), @NodeName NVARCHAR(100))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @xml XML = CAST(@xmlString AS XML);
SET @xml.modify('delete //*[local-name() = sql:variable("@NodeName")]');
    RETURN CAST(@xml AS NVARCHAR(MAX));
END;
GO
IF EXISTS (select 1 from sys.objects WHERE type = 'FN' AND name = 'insertXmlNode')
DROP FUNCTION dbo.insertXmlNode
GO
CREATE FUNCTION dbo.insertXmlNode(@XMLString NVARCHAR(MAX), @BaseNodeName NVARCHAR(100), @NodeName NVARCHAR(100), @NodeValue NVARCHAR(100))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @xml XML = CAST(@xmlString AS XML);
DECLARE @node XML = CAST(CONCAT('<', @NodeName, '>', @NodeValue, '</', @NodeName, '>') AS XML);
SET @xml.modify('insert(sql:variable("@node")) after(//*[local-name() = sql:variable("@BaseNodeName")])[1]');
    RETURN CAST(@xml AS NVARCHAR(MAX));
END;
GO
IF EXISTS (select 1 from sys.objects WHERE type = 'FN' AND name = 'updateXmlNode')
DROP FUNCTION dbo.updateXmlNode
GO
CREATE FUNCTION dbo.updateXmlNode(@XMLString NVARCHAR(MAX), @NodeName NVARCHAR(100), @NodeValue NVARCHAR(100))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @xml XML = CAST(@xmlString AS XML);
SET @xml.modify('replace value of ((//*[local-name() = sql:variable("@NodeName")]/text())[1]) with sql:variable("@NodeValue")');
    RETURN CAST(@xml AS NVARCHAR(MAX));
END;
GO