TSQL Bulk editing XML field

XML fields in MS SQL Server are powerful yet cumbersome; the script below, albeit its limitations, manages effortless insertions, updates and deletions of a simple one level deep XML fragment. Let’s see how it works: the function receives two XML fragments, the first represents the XML data, the second, the elements that need to be changed. The function creates a cursor that fetches names and values of the elements to be changed, each of these elements is searched in the XML data: if the element does not exist it is inserted, if the element exists and its new value is empty it is deleted else the element value is updated.

Practical use

I use this function in update triggers this way:

UPDATE tbl SET xmlFld = fnEditXML(T.xmlFld, I.xmlFld) FROM inserted I INNER JOIN tbl T on I.key = T.key

The same technique is applicable to JSON fields.

-- =============================================
-- Author:	Giancarlo Trevisan
-- Create date: 2021/06/09
-- Description:	Incorporate @xmlNew into @xmlData, restricted to one level, the root node name is irrelevant
-- Usage:	select dbo.fnEditXML('', '<data><foo>2</foo><fee>ciao</fee><fuu>Nothing!</fuu></data>')
-- =============================================
CREATE FUNCTION [dbo].[fnEditXML](@xmlData xml, @xmlNew xml)
RETURNS xml
AS
BEGIN
	IF isnull(rtrim(cast(@xmlData as nvarchar(max))), '') = '' 
		RETURN @xmlNew;

	DECLARE node_cursor CURSOR FOR 
		SELECT 
			n.value('local-name(.)', 'varchar(50)') fNode,
			n.value('(.)[1]', 'nvarchar(max)') fValue
		FROM
			@xmlNew.nodes('/*/*') T(n);
	OPEN node_cursor;

	DECLARE @node varchar(50), @value nvarchar(max);
	FETCH node_cursor INTO @node, @value;
	WHILE @@FETCH_STATUS = 0 BEGIN
		IF exists(SELECT 1 FROM @xmlData.nodes('/*/*') T(n) WHERE n.value('local-name(.)', 'varchar(50)') = @node) BEGIN
			IF @value = ''
				SET @xmlData.modify('delete (/*/*[local-name()=sql:variable("@node")][1])[1]');
			ELSE
				SET @xmlData.modify('replace value of (/*/*[local-name()=sql:variable("@node")][1]/text())[1] with sql:variable("@value")');
		END ELSE BEGIN
			SET @xmlNew = '<' + @node + '>' + @value + '</' + @node + '>';
			SET @xmlData.modify('insert sql:variable("@xmlNew") into (/*)[1]');
		END;
		FETCH node_cursor INTO @node, @value;
	END;

	CLOSE node_cursor;
	DEALLOCATE node_cursor;

	RETURN @xmldata;
END