i'm working on reporting service.
i need update reports, converting reports content image type xml type.
it works fine.
i have tried below, it's not converting properly.
select convert(varchar(max), convert(varbinary(max), replace(xmldata,'<fontfamily>1</fontfamily>','<fontfamily>2</fontfamily>'))) #a here, contains updated xml data.
replace(xmldata,'<fontfamily>1</fontfamily>','<fontfamily>2</fontfamily>')) in short, want many replacement in same xml data
for example, have data below,
<textbox name="id"> <cangrow>true</cangrow> <keeptogether>true</keeptogether> <paragraphs> <paragraph> <textruns> <textrun> <value></value> <style> <fontfamily>calibri</fontfamily> <fontsize>8pt</fontsize> </style> </textrun> </textruns> <style> <textalign>right</textalign> </style> </paragraph> </paragraphs> <rd:defaultname></rd:defaultname> </textbox> here need replace font family font size , many more.
so how can many replacement xml data? can please guide me.
the data type image deprecated...
as looks not converting xml (n)varchar. 1 can modify xml data on string level using string methods replace, - in cases - it's better use real xml xml dml methods...
this small example should show casts might need:
--this mock of table image column 2 rows declare @tbl table(id int,xmldata image); insert @tbl values (1,'<root><somedata rowid="1"><fontfamily>1</fontfamily></somedata></root>') ,(2,'<root><somedata rowid="2"><fontfamily>2</fontfamily></somedata></root>'); --the xmldata casted varchar(max) xml --the result filled new table #tmptable select id ,xmldata imagetype ,cast(xmldata varbinary(max)) varbinarytype ,cast(cast(xmldata varbinary(max)) varchar(max)) varcharmaxtype ,cast(cast(xmldata varbinary(max)) xml) xmltype #tmptable @tbl; --this actual content select * #tmptable; --modify string method update #tmptable set varcharmaxtype=replace(varcharmaxtype,'<fontfamily>1</fontfamily>','<fontfamily>99</fontfamily>'); --modify xml-dml method update #tmptable set xmltype.modify('replace value of (/root/somedata[@rowid=1]/fontfamily/text())[1] 99'); --the updated content select * #tmptable; --and converted image select id ,imagetype ,cast(varcharmaxtype image) varcharmaxasimage ,cast(cast(xmltype varchar(max)) image) xmltypeasimage #tmptable; go --clean drop table #tmptable; 