By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
DECLARE @xml xml = N'<Krishna>select ''line1'',</Krishna>
<Krishna> ''line2''</Krishna>
<Krishna>GO</Krishna>
<Krishna>select ''line3''</Krishna>
<Krishna>GO</Krishna>
';
DECLARE @sql nvarchar(max), @crsr CURSOR;
SET @crsr = CURSOR FAST_FORWARD READ_ONLY FOR
SELECT
x.krsh.query('
let $go := .
let $prev := /Krishna[. << $go][text() = "GO"][1]
return /Krishna[text() != "GO"][. << $go and not(. << $prev)]/text()
').value('text()[1]','nvarchar(max)') line
FROM @xml.nodes('/Krishna[text() = "GO"]') x(krsh);
OPEN @crsr;
GOTO Ftch;
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_executesql @sql;
ftch:
FETCH NEXT FROM @crsr
INTO @sql;
END;
| (No column name) | (No column name) |
|---|---|
| line1 | line2 |
| (No column name) |
|---|
| line3 |