none
Update XML data in SQL

    Question

  • 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 re-wording
    Wednesday, August 18, 2010 8:04 PM

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/en-us/library/ms190798.aspx

    XPath Examples
    http://msdn.microsoft.com/en-us/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 All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Tim O Thomas Thursday, August 19, 2010 7:02 PM
    Thursday, August 19, 2010 6:12 AM
  • 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
    to

    where 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
    Thursday, August 19, 2010 1:11 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")')

    Wednesday, August 18, 2010 10:25 PM
  • 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/en-us/library/ms190798.aspx

    XPath Examples
    http://msdn.microsoft.com/en-us/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 All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Tim O Thomas Thursday, August 19, 2010 7:02 PM
    Thursday, August 19, 2010 6:12 AM
  • 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
    to

    where 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
    Thursday, August 19, 2010 1:11 PM
  • Use XML data-type 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:

    http://msdn.microsoft.com/en-us/library/ms178030.aspx

    Thursday, August 19, 2010 2:46 PM
    Answerer
  • 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

    Thursday, August 19, 2010 5:05 PM
  • When I executed the above logic I got the following error:

    Conversion failed when converting the nvarchar value 'Day(s)' to data type int.

    Could you please elaborate a bit? The sample works fine on my machines (SQL Server 2005, SQL Server 2008 and SQL Server 2008 R2). It seems that the RelDateFromWhich parameter is returned.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, August 20, 2010 8:02 AM