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.
