Formatting XML with `GO` into a set of sql commands to be executed

Question

I have seen this question Replace value in XML using SQL and again I am thankful, however, I have a XML file with all the trigger creation scripts but I have not been able to execute it because of the GO.

Partial view of my XML file:

<Krishna>IF 'MY_SERVER' &lt;&gt; @@ServerName THROW 50001, 'Wrong Server!!!',1 </Krishna>
<Krishna>GO</Krishna>
<Krishna>use [DB_02]</Krishna>
<Krishna>GO</Krishna>
<Krishna>IF EXISTS (SELECT 'Radhe' FROM sys.triggers t wHERE t.[name] = 'tgr_repl_AuPair_Insert' AND CASE WHEN t.parent_id = 0 THEN t.parent_id ELSE object_id('[sub].[repl_Aupair]') END  = t.parent_id )
                                 </Krishna>
<Krishna>EXEC('BEGIN DROP TRIGGER [sub].[tgr_repl_AuPair_Insert]  END') </Krishna>
<Krishna>GO</Krishna>
<Krishna></Krishna>
<Krishna>CREATE TRIGGER  [sub].[tgr_repl_AuPair_Insert]</Krishna>
<Krishna>ON  [sub].[repl_Aupair]</Krishna>
<Krishna>FOR  INSERT, UPDATE</Krishna>

when I try to get rid of the GO, replacing it like it is suggested here, I get a different error.

DECLARE @XML3 XML
SELECT @XML3 = (SELECT a.trigger_definition AS Krishna FROM TableBackups.dbo._MMiorelli_20220615_triggerdropping_203144_2 a FOR XML PATH(''))

WHILE @xml3.exist(N'//*[text()="GO"]')=1
BEGIN
    SET @xml3.modify(N'replace value of (//*[text()="GO"]/text())[1] with ""');
END


    exec sp_execXML @dbname=N'APCore'
                   ,@XML=@xml3
                   ,@DEBUG=1
                   ,@PrintOnly=0

this is the way I am executing the commands that are within my XML:

declare @i int = 1

select @sql1 =  ''
SELECT @SQL2 = 'Radhe'

WHILE @sql2 is not null
   begin
      SELECT @sql2 = @XML.value('(/Krishna/text())[sql:variable("@i") cast as xs:int?][1]', 'varchar(max)')

      if @DEBUG=1
         PRINT COALESCE(@sql2,'@SQL2 WAS NULL' + ' -- @I IS ' + CAST(@I AS VARCHAR(5)))

      if @sql2 is not null
         begin 

           SET @sql1 = CAST (@sql1 + @sql2 + @vbCrLf AS NVARCHAR(MAX))
          IF @PrintOnly = 1
             BEGIN

                EXEC sp_DisplayNVarchar @textToDisplay = @SQL2, @debug =0

             END 
          ELSE
             BEGIN

                EXEC (@SQL2)  
             
             END

         end

      SELECT @i = @i + 1
              
      if @i >= @Limit
         SET @sql2 = null
  end

BASICALLY:

each line of the XML is a command

SELECT @sql2 = @XML.value('(/Krishna/text())[sql:variable("@i") cast as xs:int?][1]', 'varchar(max)')

My question is: How could I replace the every GO inside my long script into a new line in my XML?

Every time I meet a GO, that GO is removed but from that place on is a new line in my XML.

this is an example of code and XML that works:

here is the code:

---------------------------------------
----check the data
---------------------------------------
GO
SELECT [@@TRANCOUNT]=@@TRANCOUNT
TRUNCATE TABLE #the_inserts
TRUNCATE TABLE #VICASA477
INSERT INTO  #the_inserts(RADHE1)
SELECT RADHE1='use apcore;' + CHAR(10)+CHAR(13) + 'exec sp_count ' + '''' +  E.AP_NAME2 + ''''
FROM #E E

DECLARE @XML3 XML
SELECT @XML3 = (SELECT #the_inserts.radhe1 AS Krishna FROM #the_inserts FOR XML PATH(''))

INSERT INTO #VICASA477
EXEC sp_execXML @dbname=N'APCore'
                ,@XML=@xml3
                ,@DEBUG=0
                ,@PrintOnly=0

select @XML3
SELECT * FROM #vicasa477
GO

Here is the XML: (partial view but you get the idea)

<Krishna>use apcore;

exec sp_count '[sub].[matchAgreementEmailSent]'</Krishna>
<Krishna>use apcore;

exec sp_count '[sub].[receivedLog]'</Krishna>
<Krishna>use apcore;

exec sp_count '[sub].[repl_Airline]'</Krishna>
<Krishna>use apcore;

exec sp_count '[sub].[repl_Airport]'</Krishna>
<Krishna>use apcore;

exec sp_count '[sub].[repl_ArrivalCalendar]'</Krishna>
<Krishna>use apcore;

exec sp_count '[sub].[repl_Aupair]'</Krishna>
<Krishna>

and here the results: (partial view but you get the idea)

Answer 1

EDIT: As mentioned by @DavidBrowne, this answer doesn't work if changing the current database with USE is necessary.

---

You can run this script using a cursor, which executes each batch separately.

To split the batches we need to use XQuery. This is made significantly more complicated by the fact that the batches are separated by the same Krishna node again, rather than each being contained in a separate child node.

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;

db<>fiddle

The logic runs like this:

• Use .nodes to grab all Krishna nodes which contain GO.
• For each of those, we run the following XQuery expression:
• Assign the current node to $go
• Find the node previous to this which contains GO.
• Find all Krishna nodes which do not contain GO, and...
• ... are located before $go...
• ... and after $prev (if any).
• Return the inner text()
• Concatenate all the text together using .query and .value

Note: This assumes that the final node is always GO.