@xml_str.modify Syntax error

Transact-SQL
1

Hi!

New to updating XML....I am using SSMS 2018 R2 and I am getting this syntax error and hope someone could help..
I am trying to update the BackgroundColor of LightBlue to Orange.

Thank you,

Msg 102, Level 15, State 1, Line 56
Incorrect syntax near '.'.

DECLARE @xml_str xml
SET @xml_str='

Solid


Solid
1pt


Solid
1pt


Solid
1pt


Solid
1pt

LightBlue
Bottom
2pt
2pt
2pt
2pt
'

UPDATE Catalog
SET @xml_str.modify('replace value of (/RightBorder/BackgroundColor[2]/BackgrounColor/text())[1] with "Orange"')
WHERE itemID = 'B35D2DB5-A14B-443C-A3B9-FA716A3E4302'

2

put this before your update and see what is returned. This is not XML

select @xml_str

3

i think you lose formatting when you do not put 3 ticks ` (by the tilda ~ key) before and after your xml body

4

Thank you both..@Mike01...not sure why the xml converted when I pasted the code here.

5

Tahnk you @yosiasz, I did try '3' and still have the same error.

6

3 ticks ` <--- this is not a spot on your monitor :slight_smile:

7

This might me easier to explain.

I am trying to a mass update the 'background' color of all reports on our Report Server from 'lightblue' to 'orange'. The background color is stored in the 'xml' column.

USE ReportServer

SELECT ItemID
,Name
,PATH
,ContentXml = (CONVERT(XML, CONVERT(VARBINARY(MAX), Content)))
FROM Catalog c
WHERE type = 2 -- 2 = Reports
/*** BACKGROUND COLOR XML***/
AND (CONVERT(NVARCHAR(MAX),CONVERT(XML,CONVERT(VARBINARY(MAX),C.CONTENT)))) LIKE '%BackgroundColor%LightBlue%'

Any help would greatly be appreciated.
Thank you

8

That my friend is a worrisome proposal and you could hose all of your reports. Would not recommend this at all. you might want to think of making this a data driven property that you then reference in your reports.

this is what we do in our report data source stored procedure

		 case   
			when expiresindays < 0 then '#ff0000'  
			when expiresindays between 59 and 61 then '#00FF00'  
			when expiresindays between 29 and 31 then '#FFFF00'  
			when expiresindays between 13 and 15 then '#FF0000'  
			when expiresindays between 16 and 28 then '#FF0000'  
			end as color
	   from #licenses

then reference that in our SSRS reports and this is what it looks like in the xml of the report

<BackgroundColor>=Fields!Color.Value</BackgroundColor>

and remember BackgroundColor touches a lot of components in the report. so the background of the report itself, background of column headers, background of individual cells.

so next time you need to change color on components within the report you just change it at the data source/stored procedure level.

Will you first test this in your pre-prod SSRS environment?

9

Thank you for the reply. This is a great solution that we will be looking into.

Thank you again.