Split SSRS XML into chunks and store it as temporary table records

Use case is known T-SQL limitation: The pattern to be searched and replaced can't exceed the maximum possible size: 8000 bytes, or 4000 Unicode characters. This applies to intrinsic functions REPLACE, CHARINDEX, PATINDEX and LIKE.

One way around is search XML using Xpath.

However, the stored procedure below performs the following:

1.       Starts at the beginning of the XML and reads 4000 characters. At this point it reads backwards to the last closed tag and stores that position e.g. may be at 3982 characters. It then stores this as the first “chunk” of XML.

2.       Then it continues to read the next 4000 characters from the last end position (e.g. was 3982 so would read from 3983), and at this point reads backwards again to get the next “chunk”.

3.       This process repeats until you reach the end of the XML then this allows us to read through each chunk using intrinsic T-SQL functions.

Note that this process works correctly providing all data sources within XML are no longer than the possible maximum size.  Best practice is to reside all data sources as database objects (Views, User Defined Functions or Stored Procedures).

USE ReportServer;
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_WARNINGS  OFF
GO

PRINT 'Date: ' + CONVERT(VARCHAR(24),GETDATE(),13) + '   Server Name: ' + @@servername + '   Service Name: ' + @@servicename
GO

IF EXISTS
      (SELECT *
          FROM sys.objects
          WHERE name = 'SplitXMLToTableRecords')
    drop procedure YourSchema.SplitXMLToTableRecords
    PRINT 'drop procedure YourSchema.SplitXMLToTableRecords'
GO

PRINT 'CREATE PROCEDURE YourSchema.SplitXMLToTableRecords'
GO
CREATE PROCYourSchema.SplitXMLToTableRecords @Path NVARCHAR(425)
AS
    BEGIN
        BEGIN TRY
            DECLARE @reportXML NVARCHAR(MAX)
            ,       @strXML    NVARCHAR(4000)
            ,       @lenXML    INT
            ,       @index     INT
            ,       @posXML    INT
            ,       @loopsXML  NUMERIC(10,2);

            IF OBJECT_ID('tempdb..#tblXML') IS NOT NULL
                DROP TABLE #tblXML;
            CREATE TABLE #tblXML ( indXML INT
            ,                       [Path] NVARCHAR(425)
            ,                      strXML NVARCHAR(4000)
            ,                      lenXML INT );

            SELECT @reportXML = CAST(CONVERT(XML,CONVERT(VARBINARY(MAX),Content)) AS NVARCHAR(MAX))
            FROM ReportServer.dbo.Catalog
            WHERE [Path] LIKE @path;

            SET @loopsXML = CEILING((SELECT LEN(CAST(@reportXML AS NVARCHAR(MAX))))/CAST(4000 AS NUMERIC));
            SET @index = 0;
            SET @posXML = 1;

            WHILE (1 = 1) BEGIN
                SELECT @lenXML = LEN(SUBSTRING(CAST(@reportXML AS NVARCHAR(MAX)),@posXML,4001 - charindex('>',reverse(SUBSTRING(CAST(@reportXML AS NVARCHAR(MAX)),@posXML,4000))) - charindex('>',reverse(SUBSTRING(CAST(@reportXML AS NVARCHAR(MAX)),@posXML,4000 - charindex('>',reverse(SUBSTRING(CAST(@reportXML AS NVARCHAR(MAX)),@posXML,4000))))))));
                IF @lenXML <> 0    BEGIN
                    SET @index = @index + 1;
                    SET @strXML = (SELECT SUBSTRING(CAST(@reportXML AS NVARCHAR(MAX)),@posXML,4001 - charindex('>',reverse(SUBSTRING(CAST(@reportXML AS NVARCHAR(MAX)),@posXML,4000))) - charindex('>',reverse(SUBSTRING(CAST(@reportXML AS NVARCHAR(MAX)),@posXML,4000 - charindex('>',reverse(SUBSTRING(CAST(@reportXML AS NVARCHAR(MAX)),@posXML,4000))))))));
                    SET @lenXML = LEN(@strXML);
                    INSERT INTO #tblXML (    indXML
                    ,                        [Path]
                    ,                        strXML
                    ,                        lenXML)
                    VALUES (                 @index
                    ,                        @Path
                    ,                        @strXML
                    ,                        @lenXML
                    );

                    SET @reportXML = SUBSTRING(CAST(@reportXML AS NVARCHAR(MAX)),@posXML,LEN(CAST(@reportXML AS NVARCHAR(MAX))));
                    SET @posXML = @lenXML + 1;
                    IF @loopsXML = @index BREAK;
                END;
            END;
            SELECT *
            FROM #tblXML;
        END TRY
            BEGIN CATCH
                SELECT   ERROR_NUMBER() AS ErrorNumber 
                        ,ERROR_SEVERITY() AS ErrorSeverity 
                        ,ERROR_STATE() AS ErrorState 
                        ,ERROR_PROCEDURE() AS ErrorProcedure 
                        ,ERROR_LINE() AS ErrorLine 
                        ,ERROR_MESSAGE() AS ErrorMessage; 
            END CATCH;
    END;
/************************************************************************************************************************************
Test:

    IF OBJECT_ID('tempdb..#myXML') IS NOT NULL    DROP TABLE #myXML;
        CREATE TABLE #myXML ( indXML INT
        ,                      strXML NVARCHAR(4000)
        ,                      lenXML INT );

    INSERT INTO #myXML
            ( indXML, strXML, lenXML )
    EXEC ReportServer.YourSchema.SplitXMLToTableRecords 'YourPath';

    SELECT *
    FROM #myXML;

*************************************************************************************************************************************/
GO

If you find this script useful please donate generously.