Answered by:
Update XML data in SQL

Is there an easy quick way to update an xml value based on the contents of that value....?
From my research so far this seems to be much more involved then I expected it to be.
Here is my table
CREATE TABLE [dbo].[ReportJobs]( [ReportJobId] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, [Enabled] [bit] NOT NULL, [Description] [nvarchar](500) NOT NULL, [ReportDefId] [int] NOT NULL, [Email] [nvarchar](500) NULL, [LastRunResult] [nvarchar](50) NULL, [ScheduleId] [int] NOT NULL, [LastRun] [datetime] NULL, [NextRun] [datetime] NULL, [PersonId] [int] NULL, [Parameters] [xml] NOT NULL,
Here is an example of the xml contained in Parameters for one record.
<ReportParams> <Param Name="EntityID">103</Param> <Param Name="RelDateFromNum">3</Param> <Param Name="RelDateFromWhich">Day(s)</Param> <Param Name="RelDateToNum">0</Param> <Param Name="RelDateToWhich">Day(s)</Param> <Param Name="GeoID">288</Param> <Param Name="InboundFlag">False</Param> <Param Name="ShowTimeliness">True</Param> </ReportParams>
Here is what I'm trying to do....
What is the correct way to make the following statement?
Update ReportJobs set <ReportParams/GeoID> = 12 where <ReportParams/GeoID> = 288
Any help would be greatly appreciated.
Thanks,
tim
 Edited by Tim O Thomas Wednesday, August 18, 2010 8:26 PM rewording
Question
Answers

Hi Tim,
Please refer to the following sample:
declare @t table (col1 int,col2 xml) insert into @t values (1,'<ReportParams> <Param Name="EntityID">101</Param> <Param Name="RelDateFromNum">3</Param> <Param Name="RelDateFromWhich">Day(s)</Param> <Param Name="RelDateToNum">0</Param> <Param Name="RelDateToWhich">Day(s)</Param> <Param Name="GeoID">288</Param> <Param Name="InboundFlag">False</Param> <Param Name="ShowTimeliness">True</Param> </ReportParams>') insert into @t values (2,'<ReportParams> <Param Name="EntityID">102</Param> <Param Name="RelDateFromNum">3</Param> <Param Name="RelDateFromWhich">Day(s)</Param> <Param Name="RelDateToNum">0</Param> <Param Name="RelDateToWhich">Day(s)</Param> <Param Name="GeoID">290</Param> <Param Name="InboundFlag">False</Param> <Param Name="ShowTimeliness">True</Param> </ReportParams>') insert into @t values (3,'<ReportParams> <Param Name="EntityID">103</Param> <Param Name="RelDateFromNum">3</Param> <Param Name="RelDateFromWhich">Day(s)</Param> <Param Name="RelDateToNum">0</Param> <Param Name="RelDateToWhich">Day(s)</Param> <Param Name="GeoID">200</Param> <Param Name="InboundFlag">False</Param> <Param Name="ShowTimeliness">True</Param> </ReportParams>') update @t set col2.modify('replace value of (ReportParams/Param[@Name="GeoID"]/text())[1] with "12"') where col2.value('(ReportParams/Param[@Name="GeoID"]/text())[1]','int')>=288 select * from @t
References:
xml Data Type Methods
http://msdn.microsoft.com/enus/library/ms190798.aspxXPath Examples
http://msdn.microsoft.com/enus/library/ms256086.aspx
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the AllInOne Code Framework! If you have any feedback, please tell us. Marked as answer by Tim O Thomas Thursday, August 19, 2010 7:02 PM

Thank you for your help Jian!
I had to make one small adjustment and wanted your opinion on it.
When I executed the above logic I got the following error:
Conversion failed when converting the nvarchar value 'Day(s)' to data type int.
I'm guessing that when the expression is evaluated it is looking at the value of the field and not just the name of the node. I corrected this by simply changing
where col2.value('(ReportParams/Param[@Name="GeoID"]/text())[1]','int')>=288
towhere col2.value('(ReportParams/Param[@Name="GeoID"]/text())[1]','nvarchar(15)')=288
Is there a different way to do this or was my change correct?
Thanks for your help!
 Marked as answer by Tim O Thomas Thursday, August 19, 2010 7:02 PM
All replies

Run this to see if it works for you ....
declare @ta table(id int, cxml xml)
declare @temp xml
set @temp =
'<ReportParams>
<Param Name="EntityID">103</Param>
<Param Name="RelDateFromNum">3</Param>
<Param Name="RelDateFromWhich">Day(s)</Param>
<Param Name="RelDateToNum">0</Param>
<Param Name="RelDateToWhich">Day(s)</Param>
<Param Name="GeoID">288</Param>
<Param Name="InboundFlag">False</Param>
<Param Name="ShowTimeliness">True</Param>
</ReportParams>'
insert into @ta values(1,@temp)
select * from @ta
update @ta set cxml.modify('replace value of (ReportParams/Param[@Name="GeoID"]/text())[1] with("12")') 
Hi Tim,
Please refer to the following sample:
declare @t table (col1 int,col2 xml) insert into @t values (1,'<ReportParams> <Param Name="EntityID">101</Param> <Param Name="RelDateFromNum">3</Param> <Param Name="RelDateFromWhich">Day(s)</Param> <Param Name="RelDateToNum">0</Param> <Param Name="RelDateToWhich">Day(s)</Param> <Param Name="GeoID">288</Param> <Param Name="InboundFlag">False</Param> <Param Name="ShowTimeliness">True</Param> </ReportParams>') insert into @t values (2,'<ReportParams> <Param Name="EntityID">102</Param> <Param Name="RelDateFromNum">3</Param> <Param Name="RelDateFromWhich">Day(s)</Param> <Param Name="RelDateToNum">0</Param> <Param Name="RelDateToWhich">Day(s)</Param> <Param Name="GeoID">290</Param> <Param Name="InboundFlag">False</Param> <Param Name="ShowTimeliness">True</Param> </ReportParams>') insert into @t values (3,'<ReportParams> <Param Name="EntityID">103</Param> <Param Name="RelDateFromNum">3</Param> <Param Name="RelDateFromWhich">Day(s)</Param> <Param Name="RelDateToNum">0</Param> <Param Name="RelDateToWhich">Day(s)</Param> <Param Name="GeoID">200</Param> <Param Name="InboundFlag">False</Param> <Param Name="ShowTimeliness">True</Param> </ReportParams>') update @t set col2.modify('replace value of (ReportParams/Param[@Name="GeoID"]/text())[1] with "12"') where col2.value('(ReportParams/Param[@Name="GeoID"]/text())[1]','int')>=288 select * from @t
References:
xml Data Type Methods
http://msdn.microsoft.com/enus/library/ms190798.aspxXPath Examples
http://msdn.microsoft.com/enus/library/ms256086.aspx
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the AllInOne Code Framework! If you have any feedback, please tell us. Marked as answer by Tim O Thomas Thursday, August 19, 2010 7:02 PM

Thank you for your help Jian!
I had to make one small adjustment and wanted your opinion on it.
When I executed the above logic I got the following error:
Conversion failed when converting the nvarchar value 'Day(s)' to data type int.
I'm guessing that when the expression is evaluated it is looking at the value of the field and not just the name of the node. I corrected this by simply changing
where col2.value('(ReportParams/Param[@Name="GeoID"]/text())[1]','int')>=288
towhere col2.value('(ReportParams/Param[@Name="GeoID"]/text())[1]','nvarchar(15)')=288
Is there a different way to do this or was my change correct?
Thanks for your help!
 Marked as answer by Tim O Thomas Thursday, August 19, 2010 7:02 PM

Use XML datatype method exist in the WHERE clause. It's more efficient and avoids this problem completely:
update @t set col2.modify('replace value of (ReportParams/Param[@Name="GeoID"]/text())[1] with "12"') where col2.exist('(ReportParams/Param[@Name="GeoID"])[1] >= 288 ' ) = 1
As per this article:

Thanks for the feedback Bob!
I would like to use this method, it seems cleaner but when I tried it compared to my previous query I did not get concistant results. Please see my examples below.
SELECT [ReportJobId] ,[Parameters] FROM [TLI_AuditInvoice].[dbo].[ReportJobs] where Parameters.exist('(ReportParams/Param[@Name="GeoID"])[1] = 288 ' ) = 1  RETURNS INCORRECT RESULTS ... 157 (ALL) ROWS ARE RETURNED SELECT [ReportJobId] ,[Parameters] FROM [TLI_AuditInvoice].[dbo].[ReportJobs] where Parameters.value('(ReportParams/Param[@Name="GeoID"]/text())[1]','nvarchar(15)')= 288  RETURNS CORRECT RESULTS ... 3 ROWS ARE RETURNED
Am I missing something Bob? I would like to use the method that does not require me to specify the data type if possible.
Thanks
tim

When I executed the above logic I got the following error:
Conversion failed when converting the nvarchar value 'Day(s)' to data type int.
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the AllInOne Code Framework! If you have any feedback, please tell us.