Quick simple suggestion

😎

USE tempdb;

GO

SET NOCOUNT ON;

DECLARE @SAMPLE_DATA TABLE(TSTR VARCHAR(100) NOT NULL);

INSERT INTO @SAMPLE_DATA(TSTR)

VALUES ('<key>Children</key><integer>2</integer>')

,('<key>Children</key><integer>123</integer>')

,('<key>Children</key><integer></integer>')

;

DECLARE @children INT = 4;

SELECT

SD.TSTR

,SUBSTRING(SD.TSTR,1,(CHARINDEX('<integer>',SD.TSTR,1) + LEN('<integer>') - 1 ))

+ CONVERT(VARCHAR(12),@children,0)

+ SUBSTRING(SD.TSTR,CHARINDEX('</integer>',SD.TSTR,1),LEN(SD.TSTR)) AS OUT_STR

FROM @SAMPLE_DATA SD;

Results

TSTR OUT_STR

------------------------------------------ ----------------------------------------

<key>Children</key><integer>2</integer> <key>Children</key><integer>4</integer>

<key>Children</key><integer>123</integer> <key>Children</key><integer>4</integer>

<key>Children</key><integer></integer> <key>Children</key><integer>4</integer>