locked
Replace XML Element key and value RRS feed

  • Question

  • Dear Experts,

    I have a XML as follows 

    <items>

    <item key = "some text1" value = "some text1" />

    <item key = "some text2" value = "some text2" />

    </items>

    Now i want to Replace Text1 with new text else. 

    I am using below but didn't help to replace it 

    declare @x xml 

    declare @oldvalue nvarchar(20) = "some text1"

    Declare @newvalue  nvarchar(20) = "NewText"

    select @x = xmldata from table; 

    set @x = @x.modify(' /items/item[@key=sql:variable("@oldvalue")]/key/text()[1]

    with (sql:variable("@newvalue"))

    ')

    Not sure what i am missing ... is it possible to replace Key /Values in the XML elements, if yes any help would be helpful 

    Thanks 


    Thank you... MOMEN

    Sunday, April 12, 2020 12:08 PM

Answers

  • Are you looking for the below?

    declare @x xml ='<items>
    <item key = "some text1" value = "some text1" />
    <item 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"))
    ')
    
    Select @x


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped.
     [Blog]  [LinkedIn]  [twitter]

    • Marked as answer by Momen Azmath Sunday, April 12, 2020 1:51 PM
    Sunday, April 12, 2020 1:40 PM

All replies

  • Are you looking for the below?

    declare @x xml ='<items>
    <item key = "some text1" value = "some text1" />
    <item 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"))
    ')
    
    Select @x


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped.
     [Blog]  [LinkedIn]  [twitter]

    • Marked as answer by Momen Azmath Sunday, April 12, 2020 1:51 PM
    Sunday, April 12, 2020 1:40 PM
  • Exactly, yes. 

    Thanks for help, that worked.


    Thank you... MOMEN

    Sunday, April 12, 2020 1:51 PM