Easy way to replace existing ssrs hyperlink URLs with required URLs

After development, during UAT/Production move we need to replace the development URL’s with UAT/Production URL’s. If the report is having more columns, then it is bit difficulty to identify which column is having hyperlink.

Easy way to identify and replace hyperlinks:

• Open the SSRS solution.
• Right click on the required report and select “View Code”.

•  Then SSRS report will open in xml format.

• Press “Ctrl+F” then “Find and Replace window” will open. Type “http”, then click on “Find Next” then it will show the URL’s which are present in the report.
• Replace the URL with required URL.

If the parameter values are available in xml file

----------------------- XML File loading into temp table ------------------------
-- Temp table creation
CREATE TABLE #Xlm
(
XML_Data xml
)

-- XML File loading
INSERT INTO #Xlm(XML_Data)
SELECT CONVERT(XML, BulkColumn) AS BulkColumn
FROM OPENROWSET(BULK 'C:\Kiran\SQLServer\SSRS\HyperLinksNew.xml', SINGLE_BLOB) AS x;


----------------------- Query to extract required URL from temp table ------------------------
DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)
SELECT @XML = XML_Data FROM #Xlm

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

SELECT URL
FROM OPENXML(@hDoc, 'Invoice/InvoiceDetail')
WITH
(
URL [varchar](100) 'URL',
Description [varchar](100) 'Description',
ActiveFlg [varchar](100) 'ActiveFlg'
)
where description = 'Google'
AND ActiveFlg = 1

EXEC sp_xml_removedocument @hDoc

If the parameter values are available in text/csv file

----------------------- File loading into temp table ------------------------

-- Temp table creation

CREATE TABLE #Hyperlink

(

Sl_Num INT

,URL VARCHAR(1000)

,Description VARCHAR(500)

,ActiveFlg SMALLINT

)

-- Temp table inserrtion

BULK

INSERT #Hyperlink

FROM 'C:\Kiran\SQLServer\SSRS\HyperLinks.csv'

WITH

(

FIRSTROW = 2,

FIELDTERMINATOR = ',',

ROWTERMINATOR = '\n'

)

----------------------- Query to extract required URL from temp table ------------------------

SELECT URL FROM #Hyperlink

WHERE Description = 'Google'

AND ActiveFlg = 1