XML value update in SQL SERVER

Question

Can someone help with the this table please, I want to change the page size value from 25 to 5000?

Select input_xml 
From create_report 
Where report_name='test report';

The above query this data

input_xml
<inputdata module="Vidoes">
   <schedule enabled="true">
   <recurrance>
   <time run="21:27"/>
   <pattern type="Daily">
   <detail>9</detail>
   </pattern>
   <daterange start="13/05/2013 00:00:00" end="Never"/>
   </recurrance>
   <disk="true" toemails="true" custompathandname="" format="PDF"/>
   </schedule>
   <params>
   <param name="summary_detail">Enhanced</param>
   <param name="reportType">VideoXML</param>
   <param name="reportLabel">Todays Videos</param>
   <param name="pageSize">25</param>
   </params>
   </inputdata>

score 3 · Answer 1 · answered Oct 15 '13 at 11:35

if @data is your xml variable:

set @data.modify('
      replace value of (inputdata/params/param[@name="pageSize"]/text())[1]
      with 5000
')

If you want to update table:

update create_report set
input_xml.modify('
      replace value of (inputdata/params/param[@name="pageSize"]/text())[1]
      with 5000
')
where report_name='test report'

sql fiddle demo

score 0 · Answer 2 · answered Oct 15 '13 at 11:48

0

It's worth to note that if you just want to replace pageSize with 25 to 5000 and you're pretty sure that won't break anything, casting XML to nvarchar(max), than using replace function and than cast nvarchar(max) to XML can result in way faster query.

update create_report
set input_xml = cast(replace(cast(input_xml as nvarchar(max)), '<param name="pageSize">25</param>', '<param name="pageSize">5000</param>') as xml)
where report_name = 'test report'

Please bear in mind that it's more suited for one-time change than to make it regularly used task or stored procedure as with changing structure of XML files, it can easily break something, so Roman Pekar's solution is safer. This should be still faster, though, so if time is of the essence...

answered Oct 15 '13 at 11:48

nimdil

1,361
10
20

This query gives me this error "Operand type clash: xml is incompatible with ntext" – Wood Chipper Oct 15 '13 at 11:54
That's before you store xml in `ntext` column rather then `xml`. This is generally TERRIBLE idea but if you're stuck with it, you can change and of second line of my code from `...) as xml)` to `...) as ntext)`. – nimdil Oct 15 '13 at 12:03
I meant "because", not "before" – nimdil Oct 15 '13 at 12:03

XML value update in SQL SERVER

2 Answers2