Update XML node using XQuery

Question

I have a XML type variable @XMLData.

DECLARE @xmlData XML 
DECLARE @tempXML XML 

SET @xmlData =N'<ArrayOfResult>
<Result>
  <ID>1</ID>
  <Text>This text should be updated to new text</Text>
</Result>
<Result>
  <ID>2</ID>
  <Text>This text is okay</Text>
</Result>
</ArrayOfResult>';

I want to update the Text of the nodes where ID is 1.

I have tried this way

SET @tempXML = @xmlData
SELECT @xmlData;

SET @tempXML.modify('replace value of (/ArrayOfResult/Result/Text/text())[1]     with ("This text is okay")');

SELECT @tempXML

But here, I have to mention the node index [1] to update first node. How can I update the Text element which have ID = 1 ?

Gottfried Lesigang · Accepted Answer · 2017-01-16 12:54:00Z

1

Try it like this:

DECLARE @xmlData XML 
DECLARE @tempXML XML 

SET @xmlData =N'<ArrayOfResult>
<Result>
  <ID>1</ID>
  <Text>This text should be updated to new text</Text>
</Result>
<Result>
  <ID>2</ID>
  <Text>This text is okay</Text>
</Result>
</ArrayOfResult>';

SET @tempXML = @xmlData
SELECT @xmlData;

--you can use a variable to pass in the id

DECLARE @id INT=1;

SET @tempXML.modify('replace value of (/ArrayOfResult/Result[ID=sql:variable("@id")]/Text/text())[1]     with ("This text is okay")');

SELECT @tempXML

answered Jan 16, 2017 at 12:54

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gottfried Lesigang Over a year ago

@NoorAShuvo, just one hint: You can introduce the text you want to replace through a variable as well. If the value is taken from a result set's column, use sql:column("ColumnName")

Collectives™ on Stack Overflow

Update XML node using XQuery

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related