none
Update XML Element based on the Where Condition RRS feed

  • Question

  • Dear Experts, 

    I have a requirement like below. In this XML i need to update the New Value Only for certain ID fields say like ID=3 

    I am trying with the below but wasn't able to achieve, any help would be appreciated

    declare @x xml ='<items>
    <item id = "1" key = "some text1" value = "some text1" />
    <item id = "2" key = "some text2" value = "some text2" />
    <item id = "3" key = "some text1" value = "some text1" />
    <item id = "4" key = "some text2" value = "some text2" />
    <item id = "5" key = "some text1" value = "some text1" />
    <item id = "6" key = "some text2" value = "some text2" />
    </items>'

    declare @oldvalue nvarchar(20) = 'some text1'
    Declare @newvalue  nvarchar(20) = 'NewText'

    set  @x.modify('replace value of (/items/item[@key=sql:variable("@oldvalue")]/@key)[1]
    with (sql:variable("@newvalue"))
    ')
    where @x.value('(/items/item@id'), 'varchar') = '5' 

    Select @x


    Thank you... MOMEN

    Monday, May 18, 2020 11:02 AM

Answers

  • You need to have the condition inside the XML DML you pass to modify:

    set  @x.modify('replace value of (/items/item[@id = "3" and @key=sql:variable("@oldvalue")]/@key)[1]
    with (sql:variable("@newvalue"))
    ')


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by Momen Azmath Tuesday, May 19, 2020 1:21 PM
    Monday, May 18, 2020 9:38 PM

All replies

  • You need to have the condition inside the XML DML you pass to modify:

    set  @x.modify('replace value of (/items/item[@id = "3" and @key=sql:variable("@oldvalue")]/@key)[1]
    with (sql:variable("@newvalue"))
    ')


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by Momen Azmath Tuesday, May 19, 2020 1:21 PM
    Monday, May 18, 2020 9:38 PM
  • Hi Momen Azmath,

    We can use replace value of to change node values. The syntax format for replace value of is as follows:

    replace value of Expression1 with Expression2

    Please reference: examples-of-using-xquery-to-update-xml-data-in-sql-server

    Best Regards.

    yuxi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, May 19, 2020 5:40 AM
  • Thanks Much. That helped. 

    Thank you... MOMEN

    Tuesday, May 19, 2020 1:21 PM