locked
Replace XML Element key and value RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    Exactly, yes. 

    Thanks for help, that worked.

    Thank you... MOMEN

    Sunday, April 12, 2020 1:51 PM